cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gerhard1957 Frequent Visitor
Frequent Visitor

1899-12-31 in time column

Hi.

I am new to Power BI. I imported data from an Excel spreadsheet which includes a time column properly formated in Excel as hh:mm. However in Power BI every time slot is shown as 1899-12-31 hh:mm for example 1899-12-31 10:00:00 AM. I merged the time column with the date column but the 1899-12-31 date remains in the new column. Please help?

1 ACCEPTED SOLUTION

Accepted Solutions
bullius Member
Member

Re: 1899-12-31 in time column

@v-shex-msft

 

That is correct.

 

However, it looks like you are changing the format in the modelling tab of the main pbi desktop window. If you change the column type in the Query Editor from Date/Time to Time, it also solves the problem.

 

Date.png

5 REPLIES 5
Gerhard1957 Frequent Visitor
Frequent Visitor

Re: 1899-12-31 in time column

OK. I found a work-around by splitting the time column and then deleting the 1899-12-31 column. However, I would still like to prevent the problem if possible.

bullius Member
Member

Re: 1899-12-31 in time column

Hi @Gerhard1957

 

Does the date: 1899-12-31 appear where there is no date value in the original data? i.e. there is just a time value?

 

Have you tried changing the field type to "Time"?

Community Support Team
Community Support Team

Re: 1899-12-31 in time column

Hi @bullius,

 

>>Does the date: 1899-12-31 appear where there is no date value in the original data? i.e. there is just a time value?

Yes, it as you said. This case also appeared when you change date format to date/time.

 

Default table:

Capture.PNG

 

Changed format:

Capture2.PNG

 

Date format convert to [date] field + 12:00 AM, time format convert to 1899-12-31 + [Time] field.

 

You can modify the format to show the correct result:

Date:

Capture3.PNG

time:

Capture4.PNG

 

Result:

Capture5.PNG

 

In summary, I think 12:00 AM and 1899-12-31 is the default value of the column which does not contain date and time value.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
bullius Member
Member

Re: 1899-12-31 in time column

@v-shex-msft

 

That is correct.

 

However, it looks like you are changing the format in the modelling tab of the main pbi desktop window. If you change the column type in the Query Editor from Date/Time to Time, it also solves the problem.

 

Date.png

Gerhard1957 Frequent Visitor
Frequent Visitor

Re: 1899-12-31 in time column

Thanks guys Smiley Happy