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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
HugoTMO
Helper I
Helper I

Calculate working hours without non-working hours, weekends and holidays

Hi community, 

So, i have this problem: 

I need to calculate working hours, considering business hour is 8:30am to 5pm, no weekends. 

If we receive a request after 5pm and its not attended the same day, it should start counting at 8:30am of the next day. 

Any help?   Thank you!!

 

(FechaSolicitudOS = start date / FechaRegistroOS= end date)

 

HugoTMO_0-1600288345455.png

 

13 REPLIES 13
lkalawski
Super User
Super User

HI @HugoTMO 

Please check this link:

https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/td-p/374255

_______________
If I helped, please accept the solution and give kudos! 😀

Hi @lkalawski 

I tried that solution and i have this issue: 

- An argument of function 'TIME' has the wrong data type or the result is too large or too small. -

 

I already verified that those columns are datetime type.. 

Any ideas?

 

HugoTMO_0-1600292649376.png

 

az38
Community Champion
Community Champion

Hi @HugoTMO 

your screenshot doesn't contain full statement. please copy-paste it as text


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Hi @az38

You are right, you'll see below the statement im using. 

Thanks!

 

Work Hour =
VAR filtered =
FILTER (
ADDCOLUMNS (
CROSSJOIN (
CALENDAR ( Vw_OperacionesOS_v2[FechaSolicitudOS], Vw_OperacionesOS_v2[FechaRegistroOS] ),
SELECTCOLUMNS ( GENERATESERIES ( 9, 18 ), "Hour", [Value] )
),
"Day of week", WEEKDAY ( [Date], 2 )
),
[Day of week] < 6
&& Vw_OperacionesOS_v2[NroOS] = EARLIER ( Vw_OperacionesOS_v2[NroOS] )
)
VAR hourcount =
COUNTROWS (
FILTER (
filtered,
(
[Date] >= DATEVALUE ( Vw_OperacionesOS_v2[FechaSolicitudOS] )
&& [Hour]
> HOUR ( Vw_OperacionesOS_v2[FechaSolicitudOS] ) + 1
)
&& (
[Date] <= DATEVALUE ( Vw_OperacionesOS_v2[FechaRegistroOS] )
&& [Hour]
> HOUR ( Vw_OperacionesOS_v2[FechaRegistroOS] ) - 1
)
)
)
VAR remained =
DATEDIFF (
TIMEVALUE ( Vw_OperacionesOS_v2[FechaSolicitudOS] ),
TIME ( HOUR ( Vw_OperacionesOS_v2[FechaSolicitudOS] ) + 1, 0, 0 ),
MINUTE
)
+ DATEDIFF (
TIME ( HOUR ( Vw_OperacionesOS_v2[FechaRegistroOS] ) - 1, 0, 0 ),
TIMEVALUE ( Vw_OperacionesOS_v2[FechaRegistroOS] ),
MINUTE
)
RETURN
IF ( hourcount <> BLANK (), (hourcount*60 + remained)/60, 0 )
az38
Community Champion
Community Champion

@HugoTMO 

the first idea is to check your Vw_OperacionesOS_v2[FechaRegistroOS] field. maybe it has not a date time values somewhere


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 

I double checked and its date-time type although there are some blank spaces in that column. 

Any other ideas?

Thanks.

Hi @HugoTMO,

 

I think you answered your question, there are nulls in the data



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


az38
Community Champion
Community Champion

@HugoTMO 

what should return your statement for these blank values?

I have tested this scenario - the same error if [Column1 has blank values

 

az38_0-1600374945238.png

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 

I´ve linked Pbi with our database, thats why i´ve got some blanks in that column. Since i am trying to measure SLA i wont take in consideration operations that have no data on that column (become from an internal process that we dont wanna control). 

I hope that answers your question, as a return i dont mind if says "Dont Use" or so. 

 

Thanks, 

az38
Community Champion
Community Champion

@HugoTMO 

so, just to filter out rows with blank Vw_OperacionesOS_v2[FechaSolicitudOS] values in Power Query or in SQL-query to data source


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 

I did what you suggested and i have the error of function "TIME". 

Maybe a problem with the code? Any value to change?

 

Thanks,

az38
Community Champion
Community Champion

@HugoTMO 

show, please, column profile from query editor

befor do not forget to refresh preview

 

az38_0-1600409307655.png

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Hi @az38 

This is what i get

Thanks for your help.

 

HugoTMO_0-1600815885636.png

 

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors