Date format for Excel export for Custom Analytical Query

[former_member306668]

Hi;

We have noticed that when exporting a Custom Analytical Query (that contains dates) to Excel, the date field is actually exported as text and Excel does not recognize it as date. It is only after the users moves the focus on a cell (with a date value) and then places the cursor into the value field that Excel recognizes the date. To check this we have added a filter to the Excel. In the image below you can see that some of the dates are correctly recognized (red arrow pointing to the ones where we have touched the cell), but others not.

Just wondering if we are the only ones experiencing this or if this is a known issue.

Regards

Thomas

[Amith_Nair · Accepted solution]

Hi Thomas,

I could not replicate the issue, when I downloaded the file, it came up in the right format with slash separator: mm/dd/yyyy.

I don't want to believe if they are field specific issues. Just wondering, how do you see those specific values in the custom Analytical report before extracting to excel? are they different from excel?

Here is what I have for the excel extract from custom Analytical Queries.

Thank you! Amith Nair

[former_member306668 · Accepted solution]

Hi Amith;

Thanks for replying to the post, you are using the US date format, most likely your Excel is also using US date format. And the issue is only visible for you once you add a filter.

Can I ask you to create a filter in Excel on row 13. Then check the filter values for the date column. My bet is that they are shown as string values and not as date values. Once you touch some of the date fields in the Excel you will see the filter changing. Some values are then shown correctly as dates and some still as String.

Regards

Thomas

[former_member306668 · Accepted solution]

See image below

[Amith_Nair · Accepted solution]

Hi Thomas,

I could not replicate the issue in my test environment. Upon further research, I found this could be a regional setting of the excel version in your computer. Please try to change the format to US English and see if this does the trick, because for every region, there is a specific date format that it comply to.

Here is an article on this: https://stackoverflow.com/questions/4660906/some-dates-recognized-as-dates-some-dates-not-recognized...

Hope this helps! Amith Nair

[former_member306668 · Accepted solution]

Thanks Amith;

We tried this as well, but still have the same issue that we actually need to click on a cell for it to be recognized as a date. Visually it looks ok, but you use the filter you can see it is still treated as a string.

Nevertheless I don't think one can expect users to use the US date format in Australia.

Regards

Thomas