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.
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
Last edited: