cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 Time End Time 6:00:00 18: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:00 6/02/2018 10:00 Work Day is 6AM-6PM. So +12 Work hours is next day at same time 6/02/2018 4:00 7/02/2018 6:00 04: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:00 7/02/2018 18:00 19: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:00 5/02/2018 17:00 02/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:00 29/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
Frequent Visitor
Community Champion

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

Community Champion

@gimpexe

Seemingly it works with the sample data you provided

But this formula might fail in some situations

Regards
Zubair

4 REPLIES 4
Frequent Visitor
Microsoft Employee

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

Community Champion

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

Community Champion

@gimpexe

Seemingly it works with the sample data you provided

But this formula might fail in some situations

Regards
Zubair

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors