Time Duration Format Error

Occasional Visitor

Excel 365 on Windows 10 
Hi can someone explain to me why this happens:

I download a report and when I view the info I see that the duration of time for the 3 metrics look like so: 


This looks fine to me as it seems to be in h:mm:ss format.
Upon further investigation in the formula bar shows it as a time with the midnight hour:


Each time I load data in Power Query (in excel or Power Bi) It formats the data to the Time format but now with a date:

I try to Change Type to "duration" but it errors out every time. What do I need to do and why is it registering this way? Is in text format or something?

1 Reply


In Power Query just extract the time in Transform tab. Duration is the difference between two datetime:s.


In general internally date is integer part of the number and time is decimal part. The difference could be only in offset, e.g. in Excel 1 is equivalent of 01 Jan 1900. If you enter time only internally is is assumed that very first date. Loading to Power Query such date is shown explicitly.