Export to excel date always includes time

[mitiefjn]

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

[jthomson]

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?

[v-yuezhe-msft · Accepted solution]

@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

[igorplotnikov]

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?

[smann]

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.

[mitiefjn]

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

[v-yuezhe-msft]

@mitiefjn,

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

Regards,
Lydia

[mitiefjn]

Thanks Lydia, in that case I'll stick to the Excel method until/if the export process is tidied up

Regards

Fred