# Converting timestamp in excel

**URL:** <https://discourse.nodered.org/t/converting-timestamp-in-excel/38537>\
**Category:** General\
**Created:** [4 January 2021 17:58 UTC](https://discourse.nodered.org/t/converting-timestamp-in-excel/38537 "2021-01-04T17:58:13Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![traderjoe](https://avatars.discourse-cdn.com/v4/letter/t/f9ae1b/32.png) [@traderjoe](https://discourse.nodered.org/u/traderjoe)\
**Post date:** [4 January 2021 17:58 UTC](https://discourse.nodered.org/t/converting-timestamp-in-excel/38537/1 "2021-01-04T17:58:13Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/c/5c012f8a2fba5a00ac74a8ec58442545eec0be79.png)

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/](https://www.epochconverter.com/) it works.

---

<div class="post-metadata">

**Author:** ![greengolfer](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/greengolfer/32/28276_2.png) [@greengolfer](https://discourse.nodered.org/u/greengolfer)\
**Post date:** [4 January 2021 19:40 UTC](https://discourse.nodered.org/t/converting-timestamp-in-excel/38537/2 "2021-01-04T19:40:44Z")

</div>

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...

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [4 January 2021 19:47 UTC](https://discourse.nodered.org/t/converting-timestamp-in-excel/38537/3 "2021-01-04T19:47:54Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [4 January 2021 21:12 UTC](https://discourse.nodered.org/t/converting-timestamp-in-excel/38537/4 "2021-01-04T21:12:39Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/3/93b362fcd80ae94a1689f9804fde8530ce9e723b.png)

---

<div class="post-metadata">

**Author:** ![traderjoe](https://avatars.discourse-cdn.com/v4/letter/t/f9ae1b/32.png) [@traderjoe](https://discourse.nodered.org/u/traderjoe)\
**Post date:** [4 January 2021 21:17 UTC](https://discourse.nodered.org/t/converting-timestamp-in-excel/38537/5 "2021-01-04T21:17:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [4 January 2021 21:24 UTC](https://discourse.nodered.org/t/converting-timestamp-in-excel/38537/6 "2021-01-04T21:24:20Z")

</div>

> [@traderjoe](#):
>
> And what is 86400

One day in seconds.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [4 January 2021 21:25 UTC](https://discourse.nodered.org/t/converting-timestamp-in-excel/38537/7 "2021-01-04T21:25:55Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [4 January 2021 22:54 UTC](https://discourse.nodered.org/t/converting-timestamp-in-excel/38537/8 "2021-01-04T22:54:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [4 January 2021 23:04 UTC](https://discourse.nodered.org/t/converting-timestamp-in-excel/38537/9 "2021-01-04T23:04:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [18 January 2021 23:04 UTC](https://discourse.nodered.org/t/converting-timestamp-in-excel/38537/10 "2021-01-18T23:04:55Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.