Date format while export in excel

[Sankha]

Hi All,

I am facing an issue while exporting data in to excel. In report I am able to trim timestamp  of a datetime field by using format() and also able to change the format as I like.

But once exporting into  excel, it is opening AS  DD/MM/YYYY  irrespective of data format in the report. My region setting in the desktop is DD/MM/YYYY. So everytime I need to change the  region setting through control panel to  view the data in desired date format. Please help. 

I need to see the  data in DD-MM-YYYY ( separator will be - in place of /) 

Regards,

[Sankha]

Hi All,

I am facing an issue while exporting data in to excel. In report I am able to trim timestamp  of a datetime field by using format() and also able to change the format as I like.

But once exporting into  excel, it is opening AS  DD/MM/YYYY  irrespective of data format in the report. My region setting in the desktop is DD/MM/YYYY. So everytime I need to change the  region setting through control panel to  view the data in desired date format. Please help. 

I need to see the  data in DD-MM-YYYY ( separator will be - in place of /) 

Regards,

[Anonymous]

How you store the data and how the data is formatted are two completely different things.  Its likely the issue you are facing is a formatting problem.

If you are using the FORMAT function, you can also specify how it displays.  This is done by placing a format string into the "Format" parameter of the FORMAT function.  An example might be:

=FORMAT(A1, "DD-MM-YYYY")

In Power BI, you could do something similar, but it will store your data as a String rather than the Date type.  Power BI has inbuilt formatting functions which has a "dd-MMM-yy" format which is the closest to your requested display format.

[Sankha]

Thanks very much for looking into this and your reply. 

If I use FORMAT (A1, "DD-MM-YYYY") it is displayed as expected in the report. Thats fine but once I exported the data into excel and opening the excel,  it always showing as DD/MM/YYYY.  Can  this data not get saved in the excel  as DD-MM-YYYY format?

[Anonymous]

That is more of an Excel behaviour.  It sees the data and then deciedes itself on what visual format it believes you will want.  You would either need to disable auto-format or append an apostrophe ( ' ) at the start of the data for excel to leave it alone.  

https://www.journalofaccountancy.com/issues/2016/dec/how-to-turn-off-excel-auto-format.html