1899-12-31 in time column

[Gerhard1957]

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?

[SKan]

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]

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

[Anonymous]

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:

Changed format:

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:

time:

Result:

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

[bullius · Accepted solution]

@Anonymous

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.

[Anonymous]

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).

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?

[Gerhard1957]

Thanks guys 

[Gerhard1957]

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.