Spreadsheet: 796452d1663278322-sum-divide-values-between-two-tables-utilization-between-dates
Sheet: Sheet1

Name | Start Date | End Date |  |  | Resource Name | Task Billable | Hours | Cell Date |  |  | KEY
Annelise Reside | 2022-01-01 00:00:00 |  |  |  | Annelise Reside | Y | 8 | 2022-02-01 00:00:00 |  |  | Eligible Hours = Working from Start Date to End Date OR Current Date (excludes Weekends and Holidays)
[NAME] | 2022-01-01 00:00:00 |  |  |  | Annelise Reside | Y | 6 | 2022-02-15 00:00:00 |  |  | Billable Hours = Hours tagged with "Y" in Task Billable column between Start and End/Current Date
[NAME] | 2022-01-01 00:00:00 | 2022-04-15 00:00:00 |  |  | Annelise Reside | N | 8 | 2022-03-15 00:00:00 |  |  | Utilization = Billable Hours /  Eligible Hours
[NAME] | 2022-04-01 00:00:00 |  |  |  | Annelise Reside | Y | 8 | 2022-04-01 00:00:00
[NAME] | 2022-01-01 00:00:00 |  |  |  | Annelise Reside | Y | 7 | 2022-07-15 00:00:00
[NAME] | 2022-01-01 00:00:00 | 2022-01-31 00:00:00 |  |  | Annelise Reside | Y | 8 | 2022-09-01 00:00:00 |  |  | MY ATTEMPT SO FAR
[NAME] | 2022-01-01 00:00:00 | 2022-06-01 00:00:00 |  |  | [NAME] | N | 8 | 2022-03-15 00:00:00 |  |  | MONTHLY UTILIZATION |  |  |  |  | QUARTERLY UTILIZATION
[NAME] | 2022-05-15 00:00:00 |  |  |  | [NAME] | Y | 8 | 2022-04-01 00:00:00 |  |  |  | Billable Hours | Eligible Hours | Utilization |  | Q1 (Jan-Mar)
Orton Timbrell | 2022-01-01 00:00:00 |  |  |  | [NAME] | Y | 7 | 2022-07-15 00:00:00 |  |  | 2022-01-01 00:00:00 |  | MAX(0,NETWORKDAYS([@[Start Date]],today()-WEEKDAY(today()),Holidays)),MAX(0,NETWORKDAYS([@[Start Date]],[@[End Date]],Holidays)))*8 |  |  | Q2 (Apr-Jun)
[NAME] | 2022-09-01 00:00:00 |  |  |  | [NAME] | Y | 8 | 2022-02-01 00:00:00 |  |  | 2022-02-01 00:00:00 |  |  |  |  | Q3 (Jul-Sep) |  | These are incorrect
 |  |  |  |  | [NAME] | Y | 6 | 2022-02-15 00:00:00 |  |  | 2022-03-01 00:00:00 |  |  |  |  | 4Q (Oct-Dec)
 |  |  |  |  | [NAME] | N | 8 | 2022-03-15 00:00:00 |  |  | 2022-04-01 00:00:00
Company Holidays |  |  |  |  | [NAME] | Y | 8 | 2022-06-01 00:00:00 |  |  | 2022-05-01 00:00:00
2022-01-01 00:00:00 | New Year's Day |  |  |  | [NAME] | Y | 7 | 2022-09-01 00:00:00 |  |  | 2022-06-01 00:00:00
2022-05-30 00:00:00 | Memorial Day |  |  |  | [NAME] | Y | 8 | 2022-01-15 00:00:00 |  |  | 2022-07-01 00:00:00
2022-07-04 00:00:00 | Independence Day |  |  |  | [NAME] | Y | 6 | 2022-02-15 00:00:00 |  |  | 2022-08-01 00:00:00
2022-11-24 00:00:00 | Thanksgiving |  |  |  | [NAME] | Y | 8 | 2022-03-15 00:00:00 |  |  | 2022-09-01 00:00:00
2022-12-26 00:00:00 | Christmas Day |  |  |  | [NAME] | Y | 8 | 2022-04-01 00:00:00 |  |  | 2022-10-01 00:00:00
 |  |  |  |  | [NAME] | Y | 7 | 2022-04-15 00:00:00 |  |  | 2022-11-01 00:00:00
 |  |  |  |  | [NAME] | Y | 8 | 2022-05-01 00:00:00 |  |  | 2022-12-01 00:00:00
 |  |  |  |  | [NAME] | Y | 6 | 2022-07-15 00:00:00
Working Days Per Month |  |  |  |  | [NAME] | Y | 8 | 2022-04-01 00:00:00
 |  |  |  |  | [NAME] | Y | 7 | 2022-07-15 00:00:00 |  |  | EXPECTED RESULT
 |  |  |  |  | [NAME] | Y | 8 | 2022-02-01 00:00:00 |  |  | Month | Utilization |  |  |  |  | Quarterly Utilization
 |  |  |  |  | [NAME] | Y | 6 | 2022-02-15 00:00:00 |  |  | 2022-01-01 00:00:00 |  | 8 billable hours / 21 Eligible days |  |  |  | Q1 (Jan-Mar) |  | math is (8+90+16) / sum(B24:B26) * 8
 |  |  |  |  | [NAME] | Y | 8 | 2022-03-15 00:00:00 |  |  | 2022-02-01 00:00:00 |  | 90 billable hours / 20 Eligible days |  |  |  | Q2 (Apr-Jun)
 |  |  |  |  | [NAME] | Y | 8 | 2022-04-01 00:00:00 |  |  | 2022-03-01 00:00:00 |  | 16 billable hours / 22 Eligible days |  |  |  | Q3 (Jul-Sep)
 |  |  |  |  | [NAME] | Y | 7 | 2022-07-15 00:00:00 |  |  | 2022-04-01 00:00:00 | on and on… |  |  |  |  | 4Q (Oct-Dec)
 |  |  |  |  | [NAME] | Y | 8 | 2022-02-01 00:00:00 |  |  | 2022-05-01 00:00:00
 |  |  |  |  | [NAME] | Y | 6 | 2022-02-15 00:00:00 |  |  | 2022-06-01 00:00:00
 |  |  |  |  | [NAME] | N | 8 | 2022-03-15 00:00:00 |  |  | 2022-07-01 00:00:00
 |  |  |  |  | [NAME] | Y | 8 | 2022-06-01 00:00:00 |  |  | 2022-08-01 00:00:00
 |  |  |  |  | [NAME] | Y | 7 | 2022-09-15 00:00:00 |  |  | 2022-09-01 00:00:00
 |  |  |  |  | [NAME] | Y | 8 | 2022-02-01 00:00:00 |  |  | 2022-10-01 00:00:00
 |  |  |  |  | [NAME] | Y | 6 | 2022-02-15 00:00:00 |  |  | 2022-11-01 00:00:00
 |  |  |  |  | [NAME] | N | 8 | 2022-03-15 00:00:00 |  |  | 2022-12-01 00:00:00
 |  |  |  |  | [NAME] | Y | 8 | 2022-04-01 00:00:00
 |  |  |  |  | [NAME] | Y | 7 | 2022-07-15 00:00:00
 |  |  |  |  | Orton Timbrell | Y | 8 | 2022-02-01 00:00:00
 |  |  |  |  | Orton Timbrell | Y | 6 | 2022-02-15 00:00:00
 |  |  |  |  | Orton Timbrell | N | 8 | 2022-03-15 00:00:00
 |  |  |  |  | Orton Timbrell | Y | 8 | 2022-04-01 00:00:00
 |  |  |  |  | Orton Timbrell | Y | 7 | 2022-07-15 00:00:00
 |  |  |  |  | [NAME] | Y | 8 | 2022-02-01 00:00:00
 |  |  |  |  | [NAME] | Y | 6 | 2022-02-15 00:00:00
 |  |  |  |  | [NAME] | N | 8 | 2022-03-15 00:00:00
 |  |  |  |  | [NAME] | Y | 8 | 2022-04-01 00:00:00
 |  |  |  |  | [NAME] | Y | 7 | 2022-07-15 00:00:00