Converting timestamp in excel

[traderjoe]

I'm collecting data into a CSV file. I have timestamp followed by my data. The issue is the timestamp.

The code works, and I'm logging the timestamp and data. But when I go to open in excel, I get the above.

Column A is the timestamp logged by node red. F is the timestamp divided by 1000 (I thought that since node red logged in milliseconds, I need to divide by 1000 to get seconds.). G is the result of F converted to format time/date. But if I enter column A or F into https://www.epochconverter.com/ it works.

[greengolfer]

If you write in the debug node is the result ok?
With the information you gave, it is difficult to say if it is on NodeRed or excel side...

[dceejay]

I don’t think excel uses epoch as a way of representing timestamps. I seem to recall there is some other calculation necessary to convert it.

[Steve-Mcl]

[traderjoe]

Thanks! That did it. Can you explain what excel was expecting how node red is saving the times stamp? And what is 86400?

[Steve-Mcl]

@traderjoe

And what is 86400

One day in seconds.

[TotallyInformation]

Been a long time since I bothered to wonder about how Excel stores dates! So I can't remember the exact details. But a date in Excel is a floating point number, in UNIX and JavaScript, it is an integer.

It uses 1st Jan. 1900 as its base date (though I think it allows negatives * unlike UNIX and JavaScript which I believe are unsigned integers). That date outputs as the number 1. UNIX and JavaScript dates use 1st Jan 1970 as their base date. JavaScript dates are 1000 times more (ms) than UNIX dates (s).

Times in Excel are represented after the decimal point.

(*) Not quite, a quick test in Excel shows that 1899-12-31 returns the number 693962.0 - oddly if you put that number into a cell and format as a date, it comes out as 31 December 3799!

[dceejay]

Ah the old Y3.8K bug... Will look forward to that. ...

[TotallyInformation]

Yes, but it must have been a nightmare in 1899!