PowerQuery | Duration more than 24 hours

[ovetteabejuela]

As shown above the duration 85h 14m 16s is represented in the background as 1/3/1900  1:14:16 PM.

PowerQuery captures the 1/3/1900  1:14:16 PM value, now I'm having trouble converting it back to 85H 14m 16s.

Any ideas?

Hi @MarcelBeug, have you done anything like this?

Thank you.

[ovetteabejuela · Accepted solution]

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!

[Anonymous]

DAX Mode ( In this mode you can agregate for any dimension)

QtSegundos = SUM([Seconds])

Time=
VAR Horas = INT([QtSegundos]/3600)
VAR Minutos = INT(([QtSegundos] - (Horas * 3600))/60)
VAR Segundos = MOD([QtSegundos];60)
RETURN
Horas&":"&FORMAT(Minutos;"00")&":"&FORMAT(Segundos;"00")

[ovetteabejuela]

Okay, I already have an idea, 1/1/1900 actually represents 24:00:00 so I can start from there.

[MarcelBeug]

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.

[ovetteabejuela · Accepted solution]

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!

[MarcelBeug · Accepted solution]

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:

[ovetteabejuela]

I am confident that there will be no duration that will go beyond 28 days but I will definitely take your advise plus this one is better since it's OOTB!

I will have to change my solution then... Thank you, thank you!!!