Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Gerhard1957
New Member

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

@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

View solution in original post

7 REPLIES 7
SKan
Frequent Visitor

A little bit late to the game(as the original post is from 2016:)) but i also had the same issue. I used the following expression to create a date table:

DateTable = CALENDAR (2015, 1, 1), 2021, 12, 31)
and it retrned only one date in my table ie 31/12/1899
Solution:
Use the following formula:
DateTable = CALENDAR (DATE (2015, 1, 1), DATE (2021, 12, 31))
 
bullius
Helper V
Helper V

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"?

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
If this post helps, please consider accept as solution to help other members find it more quickly.

@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

Anonymous
Not applicable

Hi,

 

I'm also new and trying to figure out why I have 13/12/1899 00:00:00 in my report. I work in an automotive industry and try to do a report on how many items we build per hour.

 

So, in the excel file I upload to PowerQuery there is a date and time in separate columns (it's an automated file) and I modified the setting to date and time (see below).

Zuzana1991_4-1602785852810.png

 

When I upload the dataset to PowerQuery, I change the setting as well to date and time. I close the Power Query Editor and in the Power PBI app I have a formula:

Nearest Whole Hour = MROUND('Nitra ABS'[Time];TIME(1;0;0))+TIME(1;0;0)
 

For this column, I changed the setting in modelling tab to Time format.

 

It seems to be working for some items ok but there are some that are shown as 31/12/1899 00:00:00.

 
 

What I am doing wrong?

 

Thanks guys Smiley Happy

Gerhard1957
New Member

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.