Date formatting when exporting data as .xlsx

App Building
1

Dear Community,

I am having trouble getting my dates to work in Excel after exporting them from apps. Both, exporting csv from table component and utils.exportData() in Buttons (.xlsx, .csv) doesn't let my Excel recognize the data as dates. My Excel will always handle the date column as if it was a string and so I have to manually bild it into a date format with Excel functions. No big problem for me but since my colleagues want to download data and convert them to pivot charts in their Excel, it is a big pain for us all.

So my question is, what am I doing wrong or did anyone already troubleshoot this problem?

I tried:

  1. formatting dates in the postgresql query
  2. formatting dates in a JS transformer
  3. all different kinds of tricks in Excel non of which worked
2

@Leon
What is the date you have in the table and what is the format you need when you download it? Can you share a screenshot/code/example?

3

Hey Scott,

so the Problem is not the format whatsoever. I can change it to any format I want using a simple transformer (like the ohne in the screenshot). The problem is, that Excel will not accept ANY format I tried as a date format. It ALWAYS recognizes it as a string, no matter what I do.

image
image900×198 17.7 KB

I then download the data using a button like explained in my question above.

This one is Excel. If I change the format from standard to date, it won´t accept it as date.

image
image1524×504 60.7 KB

Also if I pivot the data, I will see some colors seeming to come from our design theme. So I wonder what Retool might be converting the data to.
image

4

Hi @Leon,

I have no issue exporting data with moment().format(), and importing that into Excel for it to recognize that it's a date. I wonder if there are any localization settings at play between the two. Can you format the dates as "MM/DD/YYYY" when exporting in Retool, and see if Excel can interpret that as expected? Once Excel knows it's a date, you can display/use it however you'd like.

-Justin

5

Hi Justin,

unfortunately, Excel won´t recognize your given format. I already tried. I am quite sure it´s some issue with the germand version of it but also typical german date formatting won´t be recognized... this is very unfortunate...

6

Hmmm not sure your OS, but I see instances of others having issues with similar Excel date formatting on Mac at least.

1 Like