Hours between timestamps

[CarlBlunck]

Hi team,

I need to get the hours in decimals between two timestamps.  What is the M code for this?

Example:

SHIFTSTARTDATETIME	SHIFTENDDATETIME	Hours
9/11/2022 2:45:00 PM	9/11/2022 11:15:00 PM	8.5
1/12/2022 10:30:00 PM	2/12/2022 7:00:00 AM	8.5

Thanks

Carl

[CarlBlunck · Accepted solution]

Solved this through a few steps:

#"Added Custom" = Table.AddColumn(#"Inserted Time1", "Custom", each [SHIFTENDDATETIME]-[SHIFTSTARTDATETIME]),
#"Inserted Hours" = Table.AddColumn(#"Added Custom", "Hours", each Duration.Hours([Custom]), Int64.Type),
#"Inserted Minutes" = Table.AddColumn(#"Inserted Hours", "Minutes", each Duration.Minutes([Custom]), Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Inserted Minutes", "Hours (30 min lunch)", each ([Hours]+([Minutes]/60))-.5),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Hours", "Minutes"})

[CarlBlunck · Accepted solution]

Solved this through a few steps:

#"Added Custom" = Table.AddColumn(#"Inserted Time1", "Custom", each [SHIFTENDDATETIME]-[SHIFTSTARTDATETIME]),
#"Inserted Hours" = Table.AddColumn(#"Added Custom", "Hours", each Duration.Hours([Custom]), Int64.Type),
#"Inserted Minutes" = Table.AddColumn(#"Inserted Hours", "Minutes", each Duration.Minutes([Custom]), Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Inserted Minutes", "Hours (30 min lunch)", each ([Hours]+([Minutes]/60))-.5),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Hours", "Minutes"})

[_AlexandreRM_]

Hello @CarlBlunck , I think that what you need is one of the Duration functions. See documentation here: https://learn.microsoft.com/en-us/powerquery-m/duration-seconds

Your function could look like:

hours = Duration.Seconds(dateTime1-dateTime2) / 3600

Hope this helps.

Alexandre

[CarlBlunck]

Thanks @_AlexandreRM_ unfortuately all of the duration functions just extract the portion of the duration out.  So duration.seconds will just give you the seconds of the calculated duration.  Which in my case, returns 0.

[_AlexandreRM_ · Accepted solution]

Ah yes, correct! Then use the Duration.TotalSeconds: https://learn.microsoft.com/en-us/powerquery-m/duration-totalseconds

It will be easier to use I think.

[CarlBlunck]

Ha it pays to scroll down and read the rest of the available functions list.  Thank mate, much cleaner.  Went with the Duration.TotalHours function as still gives decimals.