1899-12-31 in time column and 0.00 in date column

[Dilantha]

Hi Guys,

I have question about date and time field results after downlod csv file. I duplicate created_at column and changed data type as one column date and another as time.

• When view data point as table data viewed in perfectly.

• On data view mode alsi i couldnt find any issue, i also tried to change with column tools -> format , After change from this way it apply correctly,

• My question is when I download csv file its date and time column is change as this

• I tried to change format from exel file, but it need to download as

Is their anyway to fix this, Struggling from days.

Thank you

[Anonymous · Accepted solution]

Hi @Dilantha ,

Base on your description, I think it is because the export function of Power BI is not smart enough to automatically determine what type to list, and will become custom or general by default. The default date of time is 1899-12-30, and it will be displayed after exporting. Just as the default date of date is 0:00, hidden ones will be displayed.
Sorry for that you can only manually modify the format in Excel.
My test process is as follows, I hope this can give you a little help.

1. my test data in Power BI Desktop.

2. exported data in .CSV file

3. change the Format of "Date"（Enter "hhhh-mm-dd h:mm" in the "Type" cell）

4. change the Format of "Time".
(Because it is in "General" format, I split the column first and then convert the Time column to "Time" format)

Then remove the unwanted column.
Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Dilantha , set the time data as time only. and set format as hh24:mm:ss and then try. Do this in column property so that it applies everywhere.

[Dilantha]

I already tried this way, Its convert in everywhere except csv file. 

[Fowmy]

@Dilantha 

In Power Query, under Add Column Tab, click on the Date and choose Date Only to extract the Date for your date column. It should give you the expected format.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Dilantha]

Thank You,  Im sorry to say, It didnt work . And I want to modify same column, because I added date to the global slicer.