calculating billable hours by week number

DDT123

New Member
Joined
Aug 9, 2011
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Greetings! Needing a little help figuring out how to calculate billable hours from an outsourced company by week number.
The parameters are as follows:
The billing cycle is every two weeks, from the first day of the month to the 15th of the month. And the 16th day through the last day of the month.
The outsourced company has 41 employees which should be working 40 hours a week at 8 hours per day. We are open Saturdays, so there could be 6 working days in a WEEKNUM if the 1st day of the month falls on a Monday.

As a couple of examples... May 1, 2023 (WEEKNUM 19) fell on a Monday, so there were 6 possible days for 41 agents to work 5 days at 8 hours per day.
WEEKNUM 23 had 3 working days (Monday May 29 thru Wednesday May 31).

Once I have the foundation of the table I'll be able to enter our calculations for billable hours and compare to the outsourced companies billable hours.
How would I go about calculating this? I've attached what I've put together but I don't trust my formulas and would need to know a "do not exceed" amount of hours based upon the week number.

Thank you in advance for your help.
 

Attachments

  • Mr Excel 06072023.jpg
    Mr Excel 06072023.jpg
    68.2 KB · Views: 24
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
the networkdays.int function is what you need

jun23.xlsm
ABCDEFGHIJKLMNOPQRS
1
201-Jan15-Jan31-Jan01-Feb15-Feb28-Feb01-Mar15-Mar31-Mar01-Apr15-Apr30-Apr01-May15-May31-May01-Jun15-Jun30-Jun
3121413111314131213141313
4
Sheet4
Cell Formulas
RangeFormula
E2:F2E2=B2+31
E3,Q3,N3,K3,H3,B3E3=NETWORKDAYS.INTL(E2,F2,11)
F3,R3,O3,L3,I3,C3F3=NETWORKDAYS.INTL(F2+1,G2,11)
 
Upvote 0

Forum statistics

Threads
1,223,952
Messages
6,175,596
Members
452,657
Latest member
giadungthienduyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top