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

       

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    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