Subtracting dates

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

I have a number that represents date/time in the format of :
yyyymmddhhmmssiii

i.e. year month day hour minute seconds milliseconds

Any way in Excel to I guess first convert into a date format to the
milliseconds and find the difference between 2 dates?

Thanks,

Frank
 
Hi Frank!

To convert this to an Excel date / time serial number use:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2
),MID(A1,13,2))+(--RIGHT(A1,3)/(24*60*60*1000))

If you have two of these strings then the general principle is
Latest-Earliest=Elapsed Time

So from this I'd get

=(DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2))+TIME(MID(A2,9,2),MID(A2,11,
2),MID(A2,13,2))+(--RIGHT(A2,3)/(24*60*60*1000)))-(DATE(LEFT(A1,4),MID
(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2))+(--R
IGHT(A1,3)/(24*60*60*1000)))

This will return the difference in number of days. Representation of
that difference in a non-confusing Excel format will be difficult
unless you opt for a format that just shows hours, mins, sec, and
thousandths of a sec:

Example format: [hh]:mm:ss.000


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
One way:

This will convert the date/time to milliseconds.


=(DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),
MID(A1,11,2),MID(A1,13,2)))*86400000--RIGHT(A1,3)
 
Another way

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(INT(MID(A1,9,4)/100),MOD(MID(
A1,9,4),100),0)+RIGHT(A1,5)/86400000

custom format mm/dd/yy hh:mm:ss.000

obviously the date format depends on the region
 
Hi Frank!

Thanks for thanks and confirmation that it works; always useful for
Google searchers.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Frank said:
Thanks that seems to work great!!!

Norman said:
Hi Frank!

To convert this to an Excel date / time serial number use:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,
Click to expand...
2
),MID(A1,13,2))+(--RIGHT(A1,3)/(24*60*60*1000))

If you have two of these strings then the general principle is
Latest-Earliest=Elapsed Time

So from this I'd get

=(DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2))+TIME(MID(A2,9,2),MID(A2,11
Click to expand...
,
2),MID(A2,13,2))+(--RIGHT(A2,3)/(24*60*60*1000)))-(DATE(LEFT(A1,4),MI
Click to expand...
D
(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2))+(--
Click to expand...
R
IGHT(A1,3)/(24*60*60*1000)))

This will return the difference in number of days. Representation of
that difference in a non-confusing Excel format will be difficult
unless you opt for a format that just shows hours, mins, sec, and
thousandths of a sec:

Example format: [hh]:mm:ss.000
Click to expand...
Click to expand...
 
Back
Top