cancel
Showing results for
Did you mean:
Regular Visitor

## Work Hours disconsidering holidays and weekends

Hi all.

I'm a beginner in Power BI and I'd like to calculate work hours (8 to 17) between two dates disconsidering holidays and weekends. How can I do that?

Johonnatan

1 ACCEPTED SOLUTION
Community Support

Hi @Johonnatan ,

First, I refer to the holiday table here: Public holidays in the United Kingdom for 2021 - Holiday API. You can modify the table based on your scenario.

Then, based on your calendar table with a column considering if the date is a workday or not, try to create a calculated column like so:

``````Working Hours =
VAR t1 =
CALENDAR ( [Created date], [Closed date] )
VAR t2 =
FILTER (
t1,
"IsWorkDay_", LOOKUPVALUE ( Calendar[WorkDay], Calendar[Date], [Date] )
),
[IsWorkDay_]
)
VAR Days_ =
COUNTROWS ( t2 )
VAR StartWorkingDateTime =
CONVERT ( MINX ( t2, [Date] ) & " " & TIME ( 8, 0, 0 ), DATETIME )
VAR EndWorkingDateTime =
CONVERT ( MAXX ( t2, [Date] ) & " " & TIME ( 17, 0, 0 ), DATETIME )
VAR DateDiff_Start =
IF (
StartWorkingDateTime < [Created date],
DATEDIFF ( StartWorkingDateTime, [Created date], MINUTE )
)
VAR DateDiff_End =
IF (
EndWorkingDateTime > [Closed date],
DATEDIFF ( [Closed date], EndWorkingDateTime, MINUTE )
)
VAR WorkingMinutes = Days_ * 9 * 60 - DateDiff_Start - DateDiff_End
RETURN
WorkingMinutes / 60
``````

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

9 REPLIES 9
Helper I

I know this is already solved in multiple ways but I'm leaving this here in hope that it might help someone in the future 🙂

https://www.villezekeviking.com/calculating-handling-time-during-office-hours/

Super User

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Super User

I'm not sure this is the best solution or not, but try it:

``````Work Hour =
VAR _DB =
DATEDIFF ( [Created date], [Closed date], DAY ) - 1
VAR _DBH =
IF ( ISBLANK ( _DB ) || _DB = 1, 0, _DB * 9 )
VAR _TD1 =
TIME ( HOUR ( [Created date] ), MINUTE ( [Created date] ), SECOND ( [Created date] ) )
VAR _TDE =
TIME ( HOUR ( [Closed date] ), MINUTE ( [Closed date] ), SECOND ( [Closed date] ) )
VAR _ET =
TIME ( 17, 00, 0 )
VAR _ST =
TIME ( 8, 00, 0 )
VAR _D1 =
IF ( _TD1 < _ST, 9, IF ( _TD1 > _ST && _TD1 < _ET, ( _ET - _TD1 ) * 24, 0 ) )
VAR _DE =
IF ( _TDE < _ST, 0, IF ( _TDE > _ST && _TDE <= _ET, ( _TDE - _ST ) * 24, if(_TDE > _ET,9,0)))
VAR _Dur =
IF ( ISBLANK ( _DB ) || _DB < 0, 0, _DBH + _D1 + _DE )
VAR _HH =
TRUNC ( _Dur )
VAR _Min =
TRUNC ( ( _Dur - _HH ) * 60 )
RETURN
_HH & ":" & _Min``````

Output:

If you want to exclude Weekends and Holidays, you need to use a calendar table.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Regular Visitor

Hi @VahidDM

I appreciate your comments. So, for normal days, this script works fine but for weekends and holidays not yet. I've created a calendar with a column considering if the date is a workday or not and I'm excluding it from datadiff line.

However, the DAX still considering the time from not workday date.

Community Support

Hi @Johonnatan ,

First, I refer to the holiday table here: Public holidays in the United Kingdom for 2021 - Holiday API. You can modify the table based on your scenario.

Then, based on your calendar table with a column considering if the date is a workday or not, try to create a calculated column like so:

``````Working Hours =
VAR t1 =
CALENDAR ( [Created date], [Closed date] )
VAR t2 =
FILTER (
t1,
"IsWorkDay_", LOOKUPVALUE ( Calendar[WorkDay], Calendar[Date], [Date] )
),
[IsWorkDay_]
)
VAR Days_ =
COUNTROWS ( t2 )
VAR StartWorkingDateTime =
CONVERT ( MINX ( t2, [Date] ) & " " & TIME ( 8, 0, 0 ), DATETIME )
VAR EndWorkingDateTime =
CONVERT ( MAXX ( t2, [Date] ) & " " & TIME ( 17, 0, 0 ), DATETIME )
VAR DateDiff_Start =
IF (
StartWorkingDateTime < [Created date],
DATEDIFF ( StartWorkingDateTime, [Created date], MINUTE )
)
VAR DateDiff_End =
IF (
EndWorkingDateTime > [Closed date],
DATEDIFF ( [Closed date], EndWorkingDateTime, MINUTE )
)
VAR WorkingMinutes = Days_ * 9 * 60 - DateDiff_Start - DateDiff_End
RETURN
WorkingMinutes / 60
``````

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Resolver II

@Johonnatan Have a couple of questions,

1. I'd like to calculate work hours (8 to 17) between two dates ==>does 2 dates mean "Created date" and "Closed Date"? /   what is formula to to calculate work hours?

2. How did you get 5:10AM at first row in your sample data?

Regular Visitor

"Created date" is a date&time of a new register is opened
"Closed Date" is a date&time of the process is completed

Basically, the formula should be the difference in hours considering only range time 08:00 am to 05:00 pm, I mean, even if the register has been opened 05:00 am I can only count as a work hour after 08:00 am.

2 - For example

Resolver II

@Johonnatan  Hi, Please share sample data thanks.

Regular Visitor

I would like to consider only weekdays between a time range disregarding holidays.

Johonnatan

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

#### Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!