Date to Millisecond formula in excel

[ExcelNovice]

I'm using the following formula:

=(E8/1000 + ("1/1/1970"-"1/1/1900"+1)*86400)/86400

to convert the numbers below to date format:

1459111252898

The formula works, but the time is off by 6 hours.

The answer I get when I use the formula is: [TABLE="width: 111"]
<tbody>[TR]
[TD]3/27/16 8:40 PM[/TD]
[/TR]
</tbody>[/TABLE]
The correct answer should be 3/27/16 2:40 PM.

Can someone help me adjust the formula to reflect the correct time?

Thanks for your help.

 

[joeu2004]

I'm using the following formula:
=(E8/1000 + ("1/1/1970"-"1/1/1900"+1)*86400)/86400
to convert the numbers below to date format:
1459111252898
[....]
The answer I get when I use the formula is: 3/27/16 8:40 PM
The correct answer should be 3/27/16 2:40 PM.

Who says?

First, you should explain the format of the time in E8 (1459111252898). You seem to assume that it is milliseconds since 1/1/1970. Is that right?

Second, if that is the case, the formula can be simplified, to wit: =E8/86400000 + "1/1/1970", unless you are using the 1904 date option in Excel, and you assume the time in E8 does as well. Is that what you assume?

Finally, in any case, 3/27/16 2:40:52.898 PM (14:20:52.898) converts to 1459089652898, not 1459111252898, expressed as milliseconds since 1/1/1970.

So your value in E8 is indeed 6 hours different from 2:40 PM.

That is no fault of the arithmetic, IMHO. Instead, the fault seems to be in your expectations.

My guess: the original time was 2:40 PM local time, but the value in E8 is based on UTC (GMT), and your local time is 6 hour behind GMT. (6 timezones west of Greenwich? I always get that mixed up.)

 

[uk747]

maybe =TEXT((E8/1000+("1/1/1970"-"1/1/1900"+1)*86400/86400),"dd/mm/yy hh:mm am/pm")

 

[joeu2004]

PS....

My guess: the original time was 2:40 PM local time, but the value in E8 is based on UTC (GMT), and your local time is 6 hour behind GMT. (6 timezones west of Greenwich? I always get that mixed up.)

Can someone help me adjust the formula to reflect the correct time?

Sorry, I overlooked that. If my conclusions are correct, you simply need to subtract 6 hours, to wit:

=E8/86400000 + "1/1/1970" - "6:0:0"
or
=E8/86400000 + ("1/1/1970" - "1/1/1900" + 1) - "6:0:0"

Again, the latter is needed only if you are using the 1904 date option in Excel.

 

[ExcelNovice]

joeu2004, it works, thanks for your help.

 

[thisoldman]

Have you corrected for the time zone and daylight savings difference from UTC? When I used my previously tested conversion formula the calculation returned the same value as your formula.

My formula,
=A1 / 86400000 + 25569
returns
2016-03-27 20:40:52.898 when formatted as "yyyy-mm-dd hh:mm:ss.000"

Currently 20:40 [8:40 PM] UTC would be 15:40 [3:40 PM] EDST [Eastern Daylight Savings Time]. Without Daylight Savings, that would be 14:40 [2:40 PM] Eastern Time, six hours difference.

Edit: Too late, already answered.