Forum Discussion
Date Time + 12 Work Hours (Operational Hours)
Hello Everyone,
I need to add 12 Work Hours (aka. Operational Hours) to a Date Time value.
My Day Start is 06:00:00 and Day End is 18:00:00, I need to exclude Weekends and Public Holidays.
How would i write the M Code/Power Query calculated column to do this?
(ie. how to i create the "Start+12WorkHours" column in the below example)
thank you
Example Data:
Work Hours Table
| Start Time | End Time |
| 6:00:00 | 18:00:00 |
Holidays List (Holidays)
| Date |
| 1/01/2018 |
| 26/01/2018 |
| 5/03/2018 |
Data Table (Data)
| Start | Start+12WorkHours | Notes |
| 5/02/2018 10:00 | 6/02/2018 10:00 | Work Day is 6AM-6PM. So +12 Work hours is next day at same time |
| 6/02/2018 4:00 | 7/02/2018 6:00 | 04:00:00 is before the work day starts (6AM). So + 12 Work hours from 06/02/2018 6AM is 07/02/2018 6AM |
| 6/02/2018 19:00 | 7/02/2018 18:00 | 19:00:00 is after the work day finishes (6PM). So + 12 Work hours from 07/02/2018 6AM is 07/02/2018 6PM |
| 2/02/2018 17:00 | 5/02/2018 17:00 | 02/02/2018 is a Friday, so need to skip over the weekend to monday's date 05/02/2018 |
| 29/12/2017 17:00 | 2/01/2018 17:00 | 29/12/2017 is a Friday and 01/01/2018 is a Public Holiday, so need to skip to Tuesday 02/01/2018 |
HI gimpexe
Try this. Might be close
Column= VAR Hour_ = HOUR ( Data[Start] ) VAR EffectiveStartDate = IF ( Hour_ < 6, Data[Start] + ( 6 - Hour_ ) / 24, IF ( Hour_ > 18, Data[Start] - ( Hour_ - 18 ) / 24, Data[Start] ) ) VAR WeekDay_ = WEEKDAY ( EffectiveStartDate, 2 ) VAR NextWorkingDay = IF ( WeekDay_ = 5, EffectiveStartDate + 3, EffectiveStartDate + 1 ) VAR Isholiday = IF ( CONTAINS ( HolidayList, HolidayList[Date], DATEVALUE ( NextWorkingDay ) ), "Holiday" ) RETURN IF ( Isholiday = "Holiday", NextWorkingDay + 1, NextWorkingDay )Seemingly it works with the sample data you provided
But this formula might fail in some situations
4 Replies
HI gimpexe
Try this. Might be close
Column= VAR Hour_ = HOUR ( Data[Start] ) VAR EffectiveStartDate = IF ( Hour_ < 6, Data[Start] + ( 6 - Hour_ ) / 24, IF ( Hour_ > 18, Data[Start] - ( Hour_ - 18 ) / 24, Data[Start] ) ) VAR WeekDay_ = WEEKDAY ( EffectiveStartDate, 2 ) VAR NextWorkingDay = IF ( WeekDay_ = 5, EffectiveStartDate + 3, EffectiveStartDate + 1 ) VAR Isholiday = IF ( CONTAINS ( HolidayList, HolidayList[Date], DATEVALUE ( NextWorkingDay ) ), "Holiday" ) RETURN IF ( Isholiday = "Holiday", NextWorkingDay + 1, NextWorkingDay )Seemingly it works with the sample data you provided
But this formula might fail in some situations
Hi gimpexe,
If you have resolved your issue, welcome to share your solution or mark the right reply as answer. More people will benefit from here, and we can close the thread. Thanks for understanding.
Best Regards,
Angelia
