cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
omarevp Regular Visitor
Regular Visitor

Working hours without non-working hours, no weekends and no holidays

Hello guys

 

Thanks for all your support.

 

I need to calculate the working hours between two dates BUT, I need it to be with non-working hours, with no weekends and with no holidays (a given list of dates)

 

My working hours are from 09:00 to 19:00, from monday to friday. everything thats outside of that timeframe should be out of the calculation. 

 

THANKS!

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Working hours without non-working hours, no weekends and no holidays

@omarevp hello, man, about this problem we have two solutioned posts:

 

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

https://community.powerbi.com/t5/Desktop/Calculating-filled-working-hours/td-p/364518

 

And This vídeo: https://www.youtube.com/watch?v=f1Si47372Pk

 

 

It will help you hot to set your work hours, and all of steps necessary to complete this problem!

 

Don't forget to mark as solution this post if you got it!

 

Best Regards,

 

 

 

7 REPLIES 7
LivioLanzo Super Contributor
Super Contributor

Re: Working hours without non-working hours, no weekends and no holidays

please post sample data

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

omarevp Regular Visitor
Regular Visitor

Re: Working hours without non-working hours, no weekends and no holidays

@LivioLanzoworking_hours is what I need, but taking these conditions:

 

working hours from 09:00 to 19:00

 

firstdate                       lastdate                     working_hours    conditions

12/06/2018 09:00       12/06/2018 16:00                               1rst: both dates are in the same day and are easy to calculate

16/07/2018 13:30       17/07/2018 10:00     6,5                        2nd: different dates  but both are working days

21/09/2018 12:00       23/09/2018 21:00     7                           3th: only takes working days, it skips weekends or holidays

null                              15/09/2018 11:00     0                           4th: if the firstdate is an empty row, shows 0

30/09/2018 14:30       29/09/2018 10:00     0                           5th: if the lastdate is previous to firstdate, shows 0

 

As you can see there are some conditions; i found similar topic in this post:

https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/m-p/374255/highlight/true

 

I tried to apply that solution, but the thing is, im having a couple of errors when the firstdate is empty, or when lastdate is previous to firstdate.

 

Im sure you can help me out.. Thanks

LivioLanzo Super Contributor
Super Contributor

Re: Working hours without non-working hours, no weekends and no holidays

You can download the file here:

 

https://1drv.ms/u/s!AiiWkkwHZChHjxZoTPCZwSgJpPvS

 

You can see the most important part happens within Power Query in reshaping your data

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Re: Working hours without non-working hours, no weekends and no holidays

@omarevp hello, man, about this problem we have two solutioned posts:

 

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

https://community.powerbi.com/t5/Desktop/Calculating-filled-working-hours/td-p/364518

 

And This vídeo: https://www.youtube.com/watch?v=f1Si47372Pk

 

 

It will help you hot to set your work hours, and all of steps necessary to complete this problem!

 

Don't forget to mark as solution this post if you got it!

 

Best Regards,

 

 

 

LivioLanzo Super Contributor
Super Contributor

Re: Working hours without non-working hours, no weekends and no holidays

Hi @omarevp

 

could you kindly mark my post as an accepted solution ( I assume it is since you kudo-ed my post )

 

thank you

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

omarevp Regular Visitor
Regular Visitor

Re: Working hours without non-working hours, no weekends and no holidays

@LivioLanzoThanks!

 

In fact, at first it worked for me, then i found some issues.. I used a previous solution, from my friend @henriquesilveir

 

I tried that solution before, but did it wrong, and this time it worked for me.. Thanks for your time, i really appreciate your help, both of you.

 

Have an excellent day!

LivioLanzo Super Contributor
Super Contributor

Re: Working hours without non-working hours, no weekends and no holidays

Could you share the issues you had with my file?

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 116 members 1,764 guests
Please welcome our newest community members: