Date format in spreadsheet file Write to xlsx

[lucabartoli]

Describe the problem/error/question

When outputting items to xlsx through the Spreadsheet file node, dates on the final spreadsheet are unformatted 1900 dates.

Everything works fine, but every time I get the resulting xlsx, I need to open it with excel and set the correct display format for the columns.

Is there a way to let the node knowing a value is a date, in order to let it use the correct t="d"?

Information on your n8n setup

• n8n version: 0.230.3
• Database (default: SQLite): SQLite
• n8n EXECUTIONS_PROCESS setting (default: own, main): main
• Running n8n via (Docker, npm, n8n cloud, desktop app): docker
• Operating system: linux

[EmeraldHerald]

Hi @lucabartoli - welcome back to the forums!

Does this post from my teammate Tom by chance fix up your issue?

XSLX to SpreadSheet Node, Encountering DATE issues

Hi @saica, this is expected as this is the type of value Excel uses to represent dates. Perhaps you want to try out this expression I posted a while back to convert such dates? Still seems to work from giving this a quick go with your first value: [grafik]

If that doesn’t help, could you share your workflow and some example data so we can see what you’re doing more in-depth?

[lucabartoli]

Hi @EmeraldHerald ,
I already tried that. Not the exact same, as my data comes from a Code block, but same concept.

Node input with JS Date transforming

[
  {
    "Name": "test",
    "Date": "2022-01-01T00:00:00.000Z"
  }
]

Output xlsx (relevant cell)
<c r="B2" t="str"><v>2022-01-01T00:00:00.000Z</v></c>

Using JS dates forces it to set an “str” type, which is worse than having the correct value with the wrong format.

[EmeraldHerald]

I’m still not too sure on exactly what you’re trying to do without a workflow @lucabartoli , but at the moment n8n doesn’t have a way to write the xlsx data type. I’m moving this over to the feature request forum - if you can provide as much detail as possible, that would be helpful for our product team

[lucabartoli]

This is a test workflow:

By downloading and opening the spreadsheet, you can easily see that the DateJS cell has the full JS date as a string, while the Date1900 one has a real excel date, just not formatted.

Changing the format in excel after opening produce the following results:

• DateJS: nothing happens, because it is declared as a string
• Date1900: correct date format is applied, but the first format on open is raw value