Forum Discussion

ovetteabejuela's avatar
ovetteabejuela
Icon for Impactful Individual rankImpactful Individual
9 years ago

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

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.

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Try adding an apostrophe infront of the data.

     

    EDIT: apostrophe is '

    • ovetteabejuela's avatar
      ovetteabejuela
      Icon for Impactful Individual rankImpactful Individual
      9 years ago

      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's avatar
        LesProud
        New Member
        8 years ago

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