Forum Discussion

mitiefjn's avatar
mitiefjn
Advocate I
7 years ago
Solved

Export to excel date always includes time

Using PBI Desktop, I have a report with several date columns, however, even though I set the Data Type to Date (without time) and the Format to my desired Date setting, when I import the CSV file into Excel the dates always appear with a time included. 

 

Is there a genuine way or format to stop this ?

 

Thanks and regards

Fred

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    mitiefjn,

    There are two methods for you.

    1. Change the data type of the date column to Short Date in Excel file.


    2. Create a calculated column in Power BI Desktop using DAX below, then export visual to csv.

    Date= FORMAT(Table1[DateCol],"mm/dd/yyyy")



    Regards,
    Lydia

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    mitiefjn,

    There are two methods for you.

    1. Change the data type of the date column to Short Date in Excel file.


    2. Create a calculated column in Power BI Desktop using DAX below, then export visual to csv.

    Date= FORMAT(Table1[DateCol],"mm/dd/yyyy")



    Regards,
    Lydia

    • mitiefjn's avatar
      mitiefjn
      Advocate I
      7 years ago

      Hi Lydia,

       

      Thanks for that, yes, the Excel option is what I've already used, but wondered if there was a PBI setting I was missing. 

       

      I'm new to PBI, so, given that I'm extracting 6 different MSProject date fields (columns), I presume I'd need to define 6 different date entries, one for each, for the PBI solution ?

       

      If that is so, I'll stick with the Excel option as it's quicker, just a shame that PBI doesn't honour the format settings when exporting.

       

      Regards

      Fred

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        mitiefjn,

        Yes. You would need to create 6 columns to convert the date columns to text format.

        Regards,
        Lydia

    • smann's avatar
      smann
      Helper I
      4 years ago

      OK but what if you're in direct query mode? I'm sorry, but this is not a good solution. If we set the format in Power BI, it's not unreasonable to expect that the format would also be applied to the export.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Is there a way to represent these values in the format "d mmm yyyy"? It seems not, because the date is turning to text this way. Are there some workarounds?

  • jthomson's avatar
    jthomson
    Solution Sage
    6 years ago

    Hi,

     

    Running across this same bug right now, I don't suppose anyone's found a simpler way to have a date field actually be a date field on export other than using the clunky method of making a bunch of calculated columns?