Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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:

 

CurrentTimeWorkHours  CurrentTime + WorkHoursNotes
07/01/20 01:00PM1207/02/20 08:00AMWork Day is 7:00AM-12:00AM. So +12H is next day at 8:00AM.
07/01/20 04:00AM307/01/20 10:00AM04: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:00PM607/13/20 09:00AM07/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:00PM707/06/20 11:00AM07/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!

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    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.

1 Reply

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    6 years ago

    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.