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.

10 REPLIES 10
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.

Regular Visitor

Hi! I saw this solution and I think it's pretty great and clean!
I just have a concern, I'm having negative values, but honestly I don't know why or how to evaluate the formula to know where is wrong.

Table Data (Created-ClosedDateVal) and Result (Working Hoours)

 Created ClosedDateVal WorkingHours 25/04/2019 19:51 26/04/2019 08:27 -0.4 31/05/2019 18:06 02/06/2019 22:17 -0.1 11/06/2019 09:09 11/06/2019 09:08 -0.016666667 18/07/2019 16:08 19/07/2019 00:15 -4.883333333 18/07/2019 16:18 19/07/2019 00:24 -4.9 18/07/2019 17:21 19/07/2019 00:13 -6.133333333 19/09/2019 16:17 20/09/2019 00:02 -5.25 26/11/2019 19:46 27/11/2019 08:22 -0.4 02/12/2019 21:57 03/12/2019 09:34 -1.383333333 25/01/2020 18:30 26/01/2020 23:45 -1052531.5 04/02/2020 08:27 04/02/2020 08:45 -1052761.45 04/02/2020 08:50 04/02/2020 08:57 -1052761.833 04/02/2020 10:08 04/02/2020 13:46 -1052763.133 04/02/2020 10:56 04/02/2020 13:46 -1052763.933 04/02/2020 15:00 04/02/2020 22:59 -1052768 18/03/2020 11:08 18/03/2020 11:59 -1053796.133 03/04/2020 20:02 06/04/2020 08:10 -0.866666667 03/04/2020 21:17 06/04/2020 08:12 -2.083333333 07/04/2020 10:17 07/04/2020 10:17 0 08/04/2020 20:35 12/04/2020 21:39 -2.583333333

Holidays

 Date Holiday 01/01/2019 Año Nuevo 04/02/2019 Aniversario de Constitución 18/03/2019 Natalicio Benito Juarez 18/04/2019 Jueves Santo 19/04/2019 Viernes Santo 01/05/2019 Dia del Trabajo 16/09/2019 Independencia de Mexico 18/11/2019 Aniversario de la Revolución Mexicana 12/12/2019 Virgen de Guadalupe 25/05/2019 Navidad 01/01/2020 Año Nuevo 04/02/2020 Aniversario de Constitución 18/03/2020 Natalicio Benito Juarez 09/04/2020 Jueves Santo 10/04/2020 Viernes Santo 01/05/2020 Dia del Trabajo 16/09/2020 Independencia de Mexico 18/11/2020 Aniversario de la Revolución Mexicana 12/12/2020 Virgen de Guadalupe 25/05/2020 Navidad

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