Forum Discussion
Solved
Split Time
I have 2 Columns with Time in and Time out in Excel which I brought into Power Query (formatted as Date/Time). I want to create a column with the hours between 7a-3p. I tried the following formula wh...
Hi,
if Time.From([Pay Rule End]) > Time.From([Pay Rule Start])
then List.Max(
{0,List.Min({15, 24*Number.From(Time.From([Pay Rule End]))})
-List.Max({7, 24*Number.From(Time.From([Pay Rule Start]))})})
else List.Max({0, 15 - 24*Number.From(Time.From([Pay Rule Start]))})
+List.Max({0, 24*Number.From(Time.From([Pay Rule End]))-7}))Stéphane
Provide some sample data and expected results and i'll have a look.
It won't be for a few hours though.
See below. Besides for the issue of getting the hours for the 11p-7a shift, the bolded times below, dont calcuate with formula above for hours between 7a-3p. Think its because shift starts the night before.
1- how can I update the above formula to include hours between 7a-3p even if shift starts night before
2 - what formula can I use to calcuate hours between 11p-7a (if I get the correct formula for 7a-3p ad 3p-7a, I really can take total hours worked minus hours from 7a-3p and 3p-11p)
Pay Rule StartPay Rule End
| 9/18/2023 23:30 | 9/19/2023 7:30 |
| 9/17/2023 6:30 | 9/17/2023 15:00 |
| 9/18/2023 23:30 | 9/19/2023 7:15 |
| 9/17/2023 6:30 | 9/17/2023 7:00 |
| 9/19/2023 6:30 | 9/19/2023 19:00 |
| 9/17/2023 5:45 | 9/17/2023 22:15 |
| 9/17/2023 6:30 | 9/17/2023 22:00 |
| 9/17/2023 6:15 | 9/17/2023 20:45 |
| 9/18/2023 6:30 | 9/18/2023 23:30 |
| 9/18/2023 6:15 | 9/18/2023 14:00 |
| 9/19/2023 5:45 | 9/19/2023 6:00 |
| 9/17/2023 6:15 | 9/17/2023 10:00 |
| 9/18/2023 6:15 | 9/18/2023 18:30 |
| 9/18/2023 6:30 | 9/18/2023 23:15 |
| 9/18/2023 23:45 | 9/19/2023 6:45 |
| 9/19/2023 23:30 | 9/20/2023 7:00 |
| 9/19/2023 23:45 | 9/20/2023 7:00 |
| 9/19/2023 23:45 | 9/20/2023 7:00 |
| 9/18/2023 23:45 | 9/19/2023 7:00 |
| 9/18/2023 6:30 | 9/18/2023 14:00 |
| 9/18/2023 23:45 | 9/19/2023 7:00 |
| 9/18/2023 23:45 | 9/19/2023 7:00 |
| 9/19/2023 6:30 | 9/19/2023 23:30 |
| 9/19/2023 6:30 | 9/19/2023 7:00 |
| 9/19/2023 6:30 | 9/19/2023 15:00 |