Date format in spreadsheet file Write to xlsx

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

Hi @lucabartoli - welcome back to the forums! :vulcan_salute:

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

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?

1 Like

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.

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 :+1:

1 Like

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

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