Import Dates from Excel - Seconds

[Stuartm1983]

Hi,

I've been importing dates with seconds from Excel, I need a process which doesn't have any manual fixes.

When I import date everything import fine, apart from the seconds. They are all defaulted to 00.

Does anyone have a solution to how the data is import in Power BI?

Thanks.

[Stuartm1983]

I can't attach a screenshot.

But the example is that

23/12/2019 23:12:12 imports as 23/12/2019 23:12:00

This happens for the whole file. However Power BI does know its a date and time stamp. 

[v-piga-msft]

Hi @Stuartm1983 ,

By my tests with the latest version of power bi desktop, I cannot reproduce your scenario.

Please check if you have installed the latest version of power bi desktop(2.71.5523.941).

In addtion, what data type of your date column in Excel file? Please set with Date type.

If you still need help, please share a sample excel file which could reproduce your issue so that I could have a test on it.

Best  Regards,

Cherry

[Stuartm1983]

Sorry about the wrong order of the screenshots I can't move them 😞

problem solved by manually formatting file in Excel

Thank for picking this up Cherry @v-piga-msft ,

Below I've attached a screenshot of our data.

Sample Data

We're running

Version: 2.70.5494.761 32-bit (June 2019)

I've asked our IT to push out the the most recent one.

By formatting column B, our problem is solved where the seconds are uploaded correctly (as seen in screenshot at top). However we do not wan't any manual process involved. Can this be solved in the Power BI end in the upload stage?

Or could you suggest any other solutions?

[Stuartm1983]

Formatted correctly left, incorrect right

Further...

When Excel has the correct format which has seconds rather than AM/PM this is different.

I would have thought Power BI would upload the same decimal number as is stored in Excel.

Why are the seconds lost at this point?

[v-piga-msft]

Hi @Stuartm1983 ,

Based on your image, it seems that your column B in excecl could load correctly in power bi.

For your column C and column D in excel which do not have seconds so when the data load in power bi , it will replace with 00 as seconds.

Best  Regards,

Cherry