Adding Work Hours to Current Time (Considering Work Hours & Holidays)

[Anonymous]

I am trying to add a column with variable number of hours to the current time. I would like to consider work hours, work days and holidays in this calculation.

Work Hours: 7AM-12AM

Work Days: Monday-Saturday

Holidays: (see below)

HolidayList

01/01/20

05/29/20

07/03/20

09/07/20

11/26/20

12/25/20

Examples:

CurrentTime	WorkHours	CurrentTime + WorkHours	Notes
07/01/20 01:00PM	12	07/02/20 08:00AM	Work Day is 7:00AM-12:00AM. So +12H is next day at 8:00AM.
07/01/20 04:00AM	3	07/01/20 10:00AM	04:00AM is before the work day starts (7:00AM). So +3H from 07/01/2018 7:00AM is 07/01/2018 10:00AM
07/11/20 08:00PM	6	07/13/20 09:00AM	07/12/2020 is a Friday, so need to skip over Sunday to Monday's date 07/13/2020. +6H ends at 07/13/20 09:00AM.
07/03/20 09:00PM	7	07/06/20 11:00AM	07/04/20 is a Holiday and 07/05/2020 is a Sunday, so we need to skip to Monday 07/06/2020. +7H ends at 07/06/20 11:00AM.

I referenced this post Date Time + 12 Work Hours (Operational Hours) , but was unable to come up with a solution to my problem. In my table all the hours for CurrentTime will be the same, but just gave some examples to show the logic.

Any help is very much appreciated!

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

You can try calculated column as below:

Result = 
var EffectiveStartDate1 = IF (
        CONTAINS ( HolidayList, HolidayList[HolidayList], DATEVALUE ('Table'[CurrentTime].[Date]) ),
       'Table'[CurrentTime]+1,'Table'[CurrentTime]
    )

VAR Hour_ =
    HOUR (EffectiveStartDate1)

VAR EffectiveStartDate2 =
    IF (
        Hour_ < 7,
        EffectiveStartDate1+(7-Hour_)/24+'Table'[WorkHours ]/24,
        IF(Hour_+'Table'[WorkHours ]>24,EffectiveStartDate1+'Table'[WorkHours ]/24+7/24)
        //... OTHER CONDITIONS
    )


VAR WeekDay_ =
    WEEKDAY ( EffectiveStartDate2, 2 )


VAR NextWorkingDay =
    IF ( WeekDay_ = 7, EffectiveStartDate2 + 1, EffectiveStartDate2  )
VAR  Isholiday =
    IF (
        CONTAINS ( HolidayList, HolidayList[HolidayList], DATEVALUE ( NextWorkingDay ) ),
        "Holiday"
    )
RETURN
IF ( Isholiday = "Holiday", NextWorkingDay + 1, NextWorkingDay )

The result will show as below:

Pbix attached 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.