13 digit date / time conversion specific to time zone

Using helper columns

timestamp in A1

C1

=INT(($A1/86400000+DATE(1970,1,1)))

in D1 (Last Sunday in October)

=EOMONTH(DATE(YEAR($C1),10,1),0)-MOD(WEEKDAY(EOMONTH(DATE(YEAR($C1),10,1),0),11),7)

in E1 (Last Sunday in March)

=EOMONTH(DATE(YEAR($C1),3,1),0)-MOD(WEEKDAY(EOMONTH(DATE(YEAR($C1),3,1),0),11),7)

in B1

=($A1/86400000+DATE(1970,1,1))+IF(AND(C1>=E1,C1<D1),TIME(3,0,0),TIME(2,0,0))

I don't know what function exits in Excel 2002 but WEEKDAY may not!

The 13 digit time stamp appears to represent milliseconds since midnight on the morning of 1 Jan 1970. I used this formula to convert to an Excel date/time serial number:

=CONVERT(timestamp,"msec","day")+DATE(1970,1,1) -- I prefer the CONVERT() function here instead of timestamp/8640000 because it reminds me that the operation is really a unit conversion from milliseconds to days.

Using the EOMONTH(....)-MOD(....) appears to be the part of John Topley's formula that handles the DST conversion. Looking at my copy of Excel 2002, the WEEKDAY() function is supported, but it only supports "return_type" values of 1, 2, or 3 (11 is not supported). Looking at the help file for the latest version (https://support.microsoft.com/en-us/...0-e404c190949a ) I cannot see what the difference is between 11 and 2 for the return_type -- they both seem to specify a Monday(1) to Sunday(7) week, so they look the same to me. Perhaps replacing the 11 in the WEEKDAY() function with 2 is all that is needed to make John's formula backwards compatible???

Edit to add: In the OP, you noted an error in the seconds/milliseconds values that are returned. I know that this formula is not trying to account for leap seconds between 1970 and now, and I know there have been several leap seconds inserted during that time. Most of the time when this question is asked, the user is not worried about leap seconds, but your note that the seconds are in error makes me wonder if you are needing to account for leap seconds. Or maybe I am reading too much into the OP.

EDIT:
I do not know if the error lies with epochconverter.com or with excel.
Assuming the last 3 digits of the 13 digit timestamp represents milliseconds as the epochconverter seem to suggest, then truncating the timestamp to omit the last 3 digits and converting that to date / time should yield ZERO milliseconds. However, that is not the case because it yields 911 milliseconds! (=1570529351/86400+DATE(1970;1;1) --> 13:09:11.911)

Admittedly, I tested on LO Calc and Gnumeric rather than Excel, but I could not replicate your errors. When I enter 1570529351871 into the formula =number/86400000+DATE(1970,1,1) formatted as ss.000 (to show only the seconds portion), I get 11.871 as expected. If I enter any random up to 13 digit number into this, I always get the same three digits for the milliseconds portion (=RANDBETWEEN(0,1E12) generates the timestamp). I have a hard time believing that Excel would make an error in a calculation as basic as this. Perhaps someone else with Excel can check, but I expect that Excel's calculation should be correct.

I'm not certain what errors @janmorris is referring to (unless it is floating point errors, but, in this case, an error of 40 milliseconds seems large for a floating point error). Ignoring the time zone change, there are really only two operations here: a division operation that would introduce a floating point error (no larger than 1E-9 days?? certainly no larger than 1 ms), and an addition operation that would propagate the floating point error but not make it worse.

Debugging this probably involves looking carefully at those two operations to see where it is getting a 40 ms error.

1570529351871/86400000=18177.4230540625 in my spreadsheet. What does your spreadsheet show? What do you get using a different calculator?
18177.4230540625+25569=43746.4230540625 in my spreadsheet. What does your spreadsheet show? What do you get using a different calculator?

I see an error in number format coding. In your file, your number format code is "dd/mm/yy hh:mm:ss\.ms". In Excel, "ms" does not mean "milliseconds". "m" means "minute" (or month, in the date portion) and "s" means seconds, so, the format code "...:ss\.ms" means "show the seconds (with leading 0 to make two digits), then show a decimal point, then show the minute again, followed by the second again. Looking at all of the examples in your picture, and notice that, in all of your examples, what we are calling "milliseconds" is really just repeating the minutes and seconds shown earlier in the display.

The correct code for "fractions of a second up to milliseconds" is "...:ss.000" or similar https://support.microsoft.com/en-us/...7-9c9354dd99f5 . I expect if you will try this format code, you will see that Excel is correctly calculating a serial number with 871 milliseconds in the C2.

Unfortunately my version won't accept this format yyyy-mm-dd hh:mm:ss.000.
I do understand, however: I am getting 12 seconds because it is rounding off 11.871 milliseconds.
So .ms translates to m=9 minutes and s=12 seconds thus the fake impression of 12 seconds and 912 milliseconds!!!!!

THANK YOU for making this clear.

Is there something I can do? (since the above format is not accepted).

My copy of 2002 has no trouble with that number format code, so I'm not sure what to recommend.

Here's my file, if it helps.

MrShorty !!!

So simple, I would have not found it if I did not download your file in the previous post!
It takes the format ...ss,000 BUT NOT the format ...ss.000 (depending on locale, I guess)

Thanks everybody!

Glad you got it to work. This forum is international (even if it's primary language is English), but I don't know how to make it and its posters (including myself) more internationally aware. We have several rules, perhaps another one that insists that posters include in their post or a signature their preferred thousands, decimal, and list separators?