Forum Discussion
Hours between timestamps
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
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"})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.
5 Replies
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) / 3600Hope this helps.
Alexandre
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.
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.
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"})