Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gimpexe
Frequent Visitor

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.

 

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 TimeEnd Time
6:00:0018: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:006/02/2018 10:00Work Day is 6AM-6PM. So +12 Work hours is next day at same time
6/02/2018 4:007/02/2018 6:0004: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:007/02/2018 18:0019: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:005/02/2018 17:0002/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:0029/12/2017 is a Friday and 01/01/2018 is a Public Holiday, so need to skip to Tuesday 02/01/2018

 

3 ACCEPTED SOLUTIONS

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 )

Regards
Zubair

Please try my custom visuals

View solution in original post

@gimpexe

 

Seemingly it works with the sample data you provided

 

But this formula might fail in some situations

 

Hours.png


Regards
Zubair

Please try my custom visuals

View solution in original post

4 REPLIES 4
gimpexe
Frequent Visitor

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

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 )

Regards
Zubair

Please try my custom visuals

@gimpexe

 

Seemingly it works with the sample data you provided

 

But this formula might fail in some situations

 

Hours.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.