Date Time + 12 Work Hours (Operational Hours)

[gimpexe]

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

[gimpexe · Accepted solution]

found this:

https://social.technet.microsoft.com/Forums/windows/en-US/e475c4f7-5b7b-426e-900d-f261c153cb1f/power...

[Zubair_Muhammad · Accepted solution]

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 )

[gimpexe · Accepted solution]

found this:

https://social.technet.microsoft.com/Forums/windows/en-US/e475c4f7-5b7b-426e-900d-f261c153cb1f/power...

[v-huizhn-msft]

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

[Zubair_Muhammad · Accepted solution]

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 )

[Zubair_Muhammad · Accepted solution]

@gimpexe

Seemingly it works with the sample data you provided

But this formula might fail in some situations