# Calculate hours with predefined Working Hours and Weekend Hours

1. ## Calculate hours with predefined Working Hours and Weekend Hours

Hello Excel Forum Community,
I have looked through threads and not finding quite what I need for this calculation. I thought that the NETWORKDAYS function would help but I see that it just calculates for a certain hours as seen in this thread but my definition is a bit different. My working hours is set starting Monday at 8:00 and ending Friday at 18:00 and weekend hours start Friday 18:00 and go to Monday 8:00. I have attached a sample of what I'm looking for - any help is much appreciated.

Thank you for taking the time to read. Have a great day!

2. ## Re: Calculate hours with predefined Working Hours and Weekend Hours

Some of your dates are 2012, I assume they should all be 2013......

If you correct those then this formula in D2 should give the correct result for weekday hours

=(INT(C2-"8:00")-WEEKDAY(C2-"8:00",3)-INT(B2-"8:00")+WEEKDAY(B2-"8:00",3))*106/7+MIN(106,(WEEKDAY(C2-"8:00",3)+MOD(C2-"8:00",1))*24)-MIN(106,(WEEKDAY(B2-"8:00",3)+MOD(B2-"8:00",1))*24)

format as number and copy down

....then use this formula in E2 to get the remaining (weekend) hours

=ROUND((C2-B2)*24-D2,9)

3. ## Re: Calculate hours with predefined Working Hours and Weekend Hours

Thanks for that formula but there are dates in 2012 and some that span 2012 to 2013. Will that change the formula by much?

4. ## Re: Calculate hours with predefined Working Hours and Weekend Hours

I have played with the formula and it works for 2012 and those dates that span 2012 to 2013. Thank you for your help!

5. ## Re: Calculate hours with predefined Working Hours and Weekend Hours

Yes, my suggested formula should work for any date period.

My comments about 2012/2013 were related to your specific examples. For the results you expected some of the years were clesrly incorrect

6. ## Re: Calculate hours with predefined Working Hours and Weekend Hours

Thank you - I see that now.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1