Date written as text in excel

In the writer feature type: have you set the data type of the date field to Date?

By default it is set to Auto, which converts it to a string.

Yup. It is set to date. It still writes as text.

I tried that, but did not use the DateTimeConverter. That way it ended up as a date in Excel.

Can you confirm it is a date? Mine looks like date, but if you click into the cell, then press enter, does it move the text to the right? Does it change the formatting? Because when I checked the excel they "look" like dates, but they're not.

Not sure how, but this is what I see in Excel:

This is a sample of a workspace I created. I have 1 field that is a date. It is @DateTimeFormat(@DateTimeNow(),%m/%d/%Y)

Notice that the date being written is left justified.

Now you can clearly see my cursor is in edit mode of that cell

after I press enter, it is changed. The date is now right justified.

What happens if you click into the cell and press enter? does it change the date? Like mine does?

Also, send me your workspace please. I would like to see if my excel comes out like yours. Here is mine. Let me know if you get what I get?

datetoexcel.fmw

I believe the DateFormatter (@DateTimeFormat) is what is converting the date to a string. Have you tried without the function or use the TimeStamper transformer instead of the AttributeCreator?

So I suspected that for the production workspace (the workspace I attached is just a sample of the issue). As I said in the original post, I created a database view so that the date format is what I need so I wouldn't have to use any transformers to modify the date in the workspace. Excel still says the data is a string.

Date_XLS_test.fmw

As @erik_jan suggested, you shouldn't format the date value into any custom format such as "dd/mm/yyyy" in the workflow. Send a date/time value with a Standard FME Date/Time Format (e.g. %Y%m%d or %Y%m%d %H:%M:%S), then FME writers including the Excel writer interpret the value as an appropriate date/time value for the destination format.

If you need to format the date value with "dd/mm/yyyy" in the destination Excel sheet, consider editing the format in the Excel writer feature type, as in:

Hi Everyone,

Thank you to @takashi and @erik_jan... I was curious about this and dug in a little bit.

@tnarladni, Seems you are correct... your first attempt wouldn't produce a date type in Excel spreadsheet. Even if you set it to Date type on the Excel Writer. In the image below, Arrow 1 indicates where you were clicking to edit the cell, then hitting enter. In your case it would certainly right-justify and be recognized as a date, however, the cell type still seems to remain Custom. The Arrow 2 indicates the current cell's data type. When looking at your column (_myDate), they all showed up as Custom, even after editing them, (at least that was my observation).

With Erik's suggestion, (see column _myTimestamp(Erik)) it certainly seemed to behave like a date in Excel. But the cell data types were still showing up as Custom... and not Date.

Takashi's suggestion of setting the desired format on the writer had the desired effect in that the cells (mostly) came out as data type of Date in Excel. I did notice something odd, and I'm not sure if Takashi saw anything like this. There were 2 rows in my testing that still came out as Custom.

In the image, the red boxes indicate the data that came out as Custom cell data types. As you can see the Writer even produced 2 rows of Custom cell types when the format was set on the Writer as Takashi suggested. This might be a bug and I'll investigate further and report back. My tests were done in FME 2019.1 (b19589). I was also overwriting the existing file.

I also found that Excel can be smart sometimes... In my case, while editing in Excel, it seemed to change cell data types to date after I edited the cell and clicked enter. This happened occasionally and didn't seem scientific. I'm not sure if this expected.

I've attached my workspace here in case you want to have a play.

Dates_to_Excel.fmw uses Customers.csv

Hi all,

This issue has been resolved in FME 2020.0 beta, which you can try out now: safe.com/beta

Custom date formats should now display properly when writing to Excel

- Andrea

Hi. Just tested 2020 beta. It did not work. same problem with the first record showing as custom instead of date

My understanding is yes it will still show as "Custom" instead of date but it will now display in the column as expected. Are you still seeing the display issue?

i'm not sure what it means to display as expected? it is supposed to be a date so it should be right-justified, but it is displaying left justified still. and now instead of just the first record being custom, it is all custom which is the opposite of fixing?

I had a similar problem with the excel writer. I didn't use the same transformers but I was also getting a date in a text format that was "not recognised" as a date in excel (although in FME in the excel writer feature type my data type of the date field was set to Date).

What, in Excel, led me to think that it was a format problem:

• Content of the cell looks like a date (in my case: 2007.05.01) but is left aligned in the cell (sign in excel that it's a text).
• Trying to change the date format (format cell, from displayed yyyy.mm.dd to dd.mm.yyyy) does not change the way the date is displayed in the cell

In my case the date value was the result of a substring function in an AttributeCreator (which led me to believe that the problem was in the data that my workflow was delivering to the writer (and not in the writer's settings)). With a quick check in the FME Data Inspector, I was able to confirm that my date was already a text in the fme workflow. (in Data Inspector, Feature Information: my date format was "string: UTF-8" instead of "64 bit integer")

I added the DateTimeParse() to the function which produces the value of the date attribute in the AttributeCreator transformer and that solved the problem:

@DateTimeParse(@Substring(@Value(fme_dataset),188,10),%Y.%m.%d)

lessons learnt:

• If, in the workflow, a date in text form is delivered to the FME excel writer, it won't miraculously turn it into a date even if Type=date in the User Attributes is selected.
• Even if the values look like dates (in my case, for example: 2019.05.01), the format doesn't necessarily match.

Hi,

Using the 2024.2.0.0 (20241102 - Build 24783 - WIN64) the issue remains. I cannot figure out how to turn the text to date when exporting to Excel.

Even copying/pasting in excel does not seem to solve the issue.

The only thing that seems to make a difference is to NOT use the DateTimeConverter.

This way the dates remain as dates.

Does anyone have a clear troubleshooting for this?