Date serial number to normal date

[Farhienza_Haikal]

I have issue that I receive the data from csv, but the date is in date serial number I need to convert that to normal date, I’ve try date & time node but it is not works

my expectation is to get date value that standard like mm-dd-yyyy

Information on your n8n setup

• **n8n version:0.158
• **Database you’re using (default: SQLite): SQLite
• **Running n8n with the execution process [own(default), main]: own
• **Running n8n via [Docker, npm, n8n.cloud, desktop app]: docker

[MutedJam]

Hey @Farhienza_Haikal, could you share the data returned by your Spreadsheet File node? Just to get an idea of the data format you are trying to read and convert. Thanks!

[Farhienza_Haikal]

Hi @MutedJam thank you for responding to my post, looking forward to solve this one.
here is the data that i’ve get, so basically i want to convert Created at from that format (date serial number) to standard date like dd-mm-yyyy format or maybe mm/dd/yyyy

[MutedJam]

Many thanks! Looking at these number I assume this is the data format used by Microsoft Excel? Unfortunately, n8n doesn’t have a built in option to convert these.

If you control the source sheet, you could explicitly store these values as texts rather than date values.

If that’s not an option and you need to convert these values in n8n, you could use a Set node and an expression like {{new Date(Math.round($json["Created at"] - 25569) * 86400 * 1000)}} (found here). This would convert the Excel value in a Date value understood by n8n:

Hope this helps! Let me know if you have any further queries on this

[Farhienza_Haikal]

Hi MutedJam

thank you for the answer will check your tips for this one.
actually the data is from our vendor data export. it is in form of .csv file

[MutedJam]

Interesting, seems they might be using a similar format here. The snippet should still work though or are you running into any trouble here?

[Farhienza_Haikal]

Hi MutedJam

This is working perfectly

thank you for your help

[MutedJam]

Glad to hear, thanks so much for confirming!

[Fernanda_Silva]

@MutedJam

{{new Date(Math.round($json[“Created at”] - 25569) * 86400 * 1000)}}

It might be useful for someone, i had to use this to set many columns from my googlesheets trigger and some of the items had null values, (because its updated by pipedrive) and without this it will return some wrong data, so i had to use this code:

example:

{{ $json[‘Cotação automática’] ? new Date(Math.round(($json[‘Cotação automática’] - 25569) * 86400 * 1000)).toISOString().slice(0, -5) : null }}