Exporting | Date(Text Format) reverts to Date(Date Format)

[ovetteabejuela]

Hi Community,

I have this calculated column:

               WeekEnding = FORMAT(DimCalendar[Date] - MOD(DimCalendar[Date]-1,7) + 6,"YYYY-MM-DD")

So my intention is really to have a date in text format. I've even set Data Type and Format to Text.

My challenge though is when I export the table ( . . . > Export Data) and now I guess this is more of an Excel question rather than PowerBI or maybe there's a way around PowerBI to overcome this.

1. When I opened the exported data (csv) in notepad, the date(string) is in its "YYYY-MM-DD".

2. BUT if I use excel to open the CSV file it turns it into m/d/yyyy.

Anyway and again maybe this is really more of an Excel inquiry and I'm reposting this in an Excel forum just thought maybe I can find an answer here.

Thank you.

[Anonymous]

Try adding an apostrophe infront of the data.

EDIT: apostrophe is '

[ovetteabejuela]

Yes, I know what you mean by this but this won't work because you'll not lose the apostrophe.

Example:

WeekEnding = CONCATENATE("'",FORMAT(DimCalendar[Date] - MOD(DimCalendar[Date]-1,7) + 6,"YYYY-MM-DD"))

Expected output(when CSV file is opened in Excel):

         YYYY-MM-DD where aposthrope should have forced the date to a text type

But what you'll see is:

         'YYYY-MM-DD, aposthrope is retained. It will only disappear if you select the cell, hit F2 and press enter key.

[LesProud]

Did you every get a solution to this, I have the same problem.