Calculating Hours Worked on Work Days Per Month

**HardlyScene** · 08-15-2022, 09:34 AM

Hello, I have been working on an excel sheet to calculate the hours worked on a job per month, evenly distributing the hours across the correct months according to what days work was performed on. Thanks to the help of an excel guru on here I was able to get the formula working without issue. However, I was then instructed that the hours should only be spread across work days (excluding weekends/holidays). I have tried using the NETWORKDAYS() function and I know it is possible, but I still am struggling to get the hours to sum to the correct amount.

Workdays816.png

Pictured above is a small recreation of my data with both versions of the formula. On top is the version that works without considering work days(=MAX(0,MIN(G$1,$B3+1)-MAX(F$1,$A3))/$D3*$C3). The bottom calculation is what I have come up with to try and only account for work days (=MAX(0,NETWORKDAYS(MAX(F$8,$A10),MIN(G$8,$B10))/$D10*$C10)). As shown the "Sum" of the hours is off on the latter formula whenever the hours span across 2 or more months. I have tried tweaking the formula many times but I just can't get it to work. The issue is that the formula appears to be adding an extra day to the month before whenever the fist of the month is considered. Any help is appreciated.

workdaysExampleNew.xlsx

**dosydos** · 08-15-2022, 09:57 AM

i looked at this quickly, but shouldnt the formula for Days worked in column D be:

Formula:

Please Login or Register  to view this content.

As the days worked should only take into consideration workdays and not weekends?

**HardlyScene** · 08-15-2022, 10:10 AM

Originally Posted by dosydos

i looked at this quickly, but shouldnt the formula for Days worked in column D be:

Formula:

Please Login or Register  to view this content.

As the days worked should only take into consideration workdays and not weekends?

Dumb oversight on my part when recreating the problem. I have corrected the mistake and now the correct issue I was referencing is showing, for some reason I cannot figure out the hours won't correctly sum!

**HardlyScene** · 08-15-2022, 02:16 PM

Small Update. It appears the issue (or part of it at least) is caused by the formula including a day from the previous month when the "Date Started" is the first of the month. Here is a link to the updated workbook. Hope this aids in a solution, thank you!

workdaysExampleUpdate.xlsx

**HardlyScene** · 08-16-2022, 08:07 AM

Any help is appreciated, if I posted the question in the wrong forum or area please let me know!

**AliGW** · 08-16-2022, 08:12 AM

You've had 100 views and only one offer of help - something may not be clear to those who have looked.

Have you included expected reults in the workbook?

**HardlyScene** · 08-16-2022, 08:35 AM

Originally Posted by AliGW

You've had 100 views and only one offer of help - something may not be clear to those who have looked.

Have you included expected reults in the workbook?

Thank you for the advice! I have updated the workbook in my post with a clear example of expected results. Also all the updated information that I have found on what the issue is.

**nick.williams** · 08-16-2022, 09:18 AM

I think you just need a -1, as the NETWORKDAYS function is inclusive. That is, =NETWORKDAYS(DATE(2022,8,15),DATE(2022,8,16)) will return 2 whereas =DATE(2022,8,16)-DATE(2022,8,15) will return 1. So it should be, in F10:

=MAX(0,NETWORKDAYS(MAX(F$8,$A10),MIN(G$8-1,$B10))/$D10*$C10)

You could also use EOMONTH instead to remove the need to always have a "next" month to refer to, like this:

=MAX(0,NETWORKDAYS(MAX(F$8,$A10),MIN(EOMONTH(F$8,0),$B10))/$D10*$C10)

**dosydos** · 08-16-2022, 09:46 AM

i went a nested if route:
in F10 use:

Formula:

Please Login or Register  to view this content.

drag across and down

**HansDouwe** · 08-16-2022, 09:54 AM

Try this formula in F12 and copy right

Formula:

Please Login or Register  to view this content.

This formula produces the same results as expected.

**HardlyScene** · 08-16-2022, 10:35 AM

Originally Posted by nick.williams

I think you just need a -1, as the NETWORKDAYS function is inclusive. That is, =NETWORKDAYS(DATE(2022,8,15),DATE(2022,8,16)) will return 2 whereas =DATE(2022,8,16)-DATE(2022,8,15) will return 1. So it should be, in F10:

=MAX(0,NETWORKDAYS(MAX(F$8,$A10),MIN(G$8-1,$B10))/$D10*$C10)

You could also use EOMONTH instead to remove the need to always have a "next" month to refer to, like this:

=MAX(0,NETWORKDAYS(MAX(F$8,$A10),MIN(EOMONTH(F$8,0),$B10))/$D10*$C10)

Thank you! both functions worked like a charm. I chose to use the second as it seems more linear in design without using the next month. I really appreciate your help thanks again saved me some grey hairs that's for sure.

**HardlyScene** · 08-16-2022, 10:39 AM

Just noticed dosydos and Hans responses both work great and thank you for the help!

Calculating Hours Worked on Work Days Per Month

LinkBack

Thread Tools

Rate This Thread

Display

Calculating Hours Worked on Work Days Per Month

Re: Calculating Hours Worked on Work Days Per Month

Re: Calculating Hours Worked on Work Days Per Month

Re: Calculating Hours Worked on Work Days Per Month

Re: Calculating Hours Worked on Work Days Per Month

Re: Calculating Hours Worked on Work Days Per Month

Re: Calculating Hours Worked on Work Days Per Month

Re: Calculating Hours Worked on Work Days Per Month

Re: Calculating Hours Worked on Work Days Per Month

Re: Calculating Hours Worked on Work Days Per Month

Re: Calculating Hours Worked on Work Days Per Month

Re: Calculating Hours Worked on Work Days Per Month

Thread Information

Users Browsing this Thread

Similar Threads

Calculating Hours worked per month in VBA Excel

Calculating Work and worked hours using Pivot

Calculating Hours worked and subtracting lunch breaks ( break depending on hours worked )

[SOLVED] How to work out number of 'days' worked based on total hours

Calculating basic hours worked, between a time range and premium hours worked

Calculating Man hours and days worked over multiple sheets

Calculate Work Days Based on Hours Worked

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions