Forum Discussion

gimpexe's avatar
gimpexe
Frequent Visitor
8 years ago
Solved

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.   H...
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

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's avatar
Zubair_Muhammad
Community Champion
8 years ago

gimpexe

 

Seemingly it works with the sample data you provided

 

But this formula might fail in some situations