Forum Discussion
PowerQuery | Duration more than 24 hours
Thanks for that note, I'll keep that in mind. Good thing I didn't convert it immediately I converted the data while it is in an unknow datatype state(text)
=(Date.Day([#"MyData"]) * 8640) + (Time.Hour([#"MyData"]) * 360) + (Time.Minute([#"MyData"]) * 60) + Time.Second([#"MyData"])
I basically converted to seconds!
Alternatively you can use:
= Duration.TotalSeconds([MyData] - #datetime(1899,12,31,0,0,0))
Notice that you will get wrong results from your solution for durations > 31 days, as the day number will become 1 for February 1, 1900. This is also the case when durations from Power Query are loaded into an Excel table.
You may be interested in this video:
Okay, I already have an idea, 1/1/1900 actually represents 24:00:00 so I can start from there.
Be careful: Excel and Power Query have different zero bases for date/times.
In Excel, 0 represents 31/12/1899 0:00; in documentation also refered to as 1900/1/0 (zero January 1900).
In Power Query, 0 represents 30/12/1899 0:00.
Why?
Because Excel regards 1900 as a leap year. so 2/29/1900 is day 60 according to Excel and 3/1/1900 is day 61.
In Power Query this is corrected (1900 is not a leap year): all dates from 3/1/1900 (March 1) have the same number in Excel and Power Query, so counting back to zero takes you to 12/30/1899 0:00 as zero base in Power Query.
If you import Excel 1/3/1900 1:14:16 PM as datetime in Power Query, it becomes 1/2/1900 1:14:16 PM.
Thanks for that note, I'll keep that in mind. Good thing I didn't convert it immediately I converted the data while it is in an unknow datatype state(text)
=(Date.Day([#"MyData"]) * 8640) + (Time.Hour([#"MyData"]) * 360) + (Time.Minute([#"MyData"]) * 60) + Time.Second([#"MyData"])
I basically converted to seconds!
Alternatively you can use:
= Duration.TotalSeconds([MyData] - #datetime(1899,12,31,0,0,0))
Notice that you will get wrong results from your solution for durations > 31 days, as the day number will become 1 for February 1, 1900. This is also the case when durations from Power Query are loaded into an Excel table.
You may be interested in this video: