Spreadsheet: 796489d1663310857-sum-divide-values-between-two-tables-utilization-between-dates-hd
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 |  | True | 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 |  | True | Utilization = Billable Hours /  Eligible Hours
[NAME] | 2022-01-01 00:00:00 |  |  |  | Annelise Reside | Y | 8 | 2022-04-01 00:00:00 |  | True
[NAME] | 2022-01-01 00:00:00 |  |  |  | Annelise Reside | Y | 7 | 2022-07-15 00:00:00 |  | True
[NAME] | 2022-01-01 00:00:00 | 2022-01-31 00:00:00 |  |  | Annelise Reside | Y | 8 | 2022-09-01 00:00:00 |  | True | 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 |  | True | MONTHLY UTILIZATION |  |  |  |  | QUARTERLY UTILIZATION |  | Billable Hours | Eligible Hours | Utilization
[NAME] | 2022-01-01 00:00:00 |  |  |  | [NAME] | Y | 8 | 2022-04-01 00:00:00 |  | True |  | Billable Hours | Eligible Hours | Utilization |  | Q1 (Jan-Mar) | 2022-01-01 00:00:00
Orton Timbrell | 2022-01-01 00:00:00 |  |  |  | [NAME] | Y | 7 | 2022-07-15 00:00:00 |  | True | 2022-01-01 00:00:00 |  |  |  |  | Q2 (Apr-Jun) | 2022-04-01 00:00:00
[NAME] | 2022-09-01 00:00:00 |  |  |  | [NAME] | Y | 8 | 2022-02-01 00:00:00 |  | True | 2022-02-01 00:00:00 |  |  |  |  | Q3 (Jul-Sep) | 2022-07-01 00:00:00
 |  |  |  |  | [NAME] | Y | 6 | 2022-02-15 00:00:00 |  | True | 2022-03-01 00:00:00 |  |  |  |  | 4Q (Oct-Dec) | 2022-10-01 00:00:00
 |  |  |  |  | [NAME] | N | 8 | 2022-03-15 00:00:00 |  | True | 2022-04-01 00:00:00 |  |  |  |  | I don't use the table A23:B35 because that table is wrong. CurYear = 0).
Company Holidays |  |  |  |  | [NAME] | Y | 8 | 2022-06-01 00:00:00 |  | True | 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 |  | True | 2022-06-01 00:00:00
2022-05-30 00:00:00 | Memorial Day |  |  |  | [NAME] | Y | 8 | 2022-01-15 00:00:00 |  | True | 2022-07-01 00:00:00
2022-07-04 00:00:00 | Independence Day |  |  |  | [NAME] | Y | 6 | 2022-02-15 00:00:00 |  | True | 2022-08-01 00:00:00
2022-11-24 00:00:00 | Thanksgiving |  |  |  | [NAME] | Y | 8 | 2022-03-15 00:00:00 |  | True | 2022-09-01 00:00:00
2022-12-26 00:00:00 | Christmas Day |  |  |  | [NAME] | Y | 8 | 2022-04-01 00:00:00 |  | True | 2022-10-01 00:00:00
 |  |  |  |  | [NAME] | Y | 7 | 2022-04-15 00:00:00 |  | True | 2022-11-01 00:00:00
 |  |  |  |  | [NAME] | Y | 8 | 2022-05-01 00:00:00 |  | True | 2022-12-01 00:00:00
This table is wrong, because CurYear = 0 |  |  |  |  | [NAME] | Y | 6 | 2022-07-15 00:00:00 |  | True
Working Days Per Month |  |  |  |  | [NAME] | Y | 8 | 2022-04-01 00:00:00 |  | True
 |  |  |  |  | [NAME] | Y | 7 | 2022-07-15 00:00:00 |  | True | EXPECTED RESULT
 |  |  |  |  | [NAME] | Y | 8 | 2022-02-01 00:00:00 |  | True | Month | Utilization |  |  |  |  | Quarterly Utilization
 |  |  |  |  | [NAME] | Y | 6 | 2022-02-15 00:00:00 |  | True | 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 |  | True | 2022-02-01 00:00:00 |  | 90 billable hours / 20 Eligible days |  |  |  | Q2 (Apr-Jun)
 |  |  |  |  | [NAME] | Y | 8 | 2022-04-01 00:00:00 |  | True | 2022-03-01 00:00:00 |  | 16 billable hours / 22 Eligible days |  |  |  | Q3 (Jul-Sep)
 |  |  |  |  | [NAME] | Y | 7 | 2022-07-15 00:00:00 |  | True | 2022-04-01 00:00:00 | on and on… |  |  |  |  | 4Q (Oct-Dec)
 |  |  |  |  | [NAME] | Y | 8 | 2022-02-01 00:00:00 |  | True | 2022-05-01 00:00:00
 |  |  |  |  | [NAME] | Y | 6 | 2022-02-15 00:00:00 |  | True | 2022-06-01 00:00:00
 |  |  |  |  | [NAME] | N | 8 | 2022-03-15 00:00:00 |  | True | 2022-07-01 00:00:00
 |  |  |  |  | [NAME] | Y | 8 | 2022-06-01 00:00:00 |  | True | 2022-08-01 00:00:00
 |  |  |  |  | [NAME] | Y | 7 | 2022-09-15 00:00:00 |  | True | 2022-09-01 00:00:00
 |  |  |  |  | [NAME] | Y | 8 | 2022-02-01 00:00:00 |  | True | 2022-10-01 00:00:00
 |  |  |  |  | [NAME] | Y | 6 | 2022-02-15 00:00:00 |  | True | 2022-11-01 00:00:00
 |  |  |  |  | [NAME] | N | 8 | 2022-03-15 00:00:00 |  | True | 2022-12-01 00:00:00
 |  |  |  |  | [NAME] | Y | 8 | 2022-04-01 00:00:00 |  | True
 |  |  |  |  | [NAME] | Y | 7 | 2022-07-15 00:00:00 |  | True
 |  |  |  |  | Orton Timbrell | Y | 8 | 2022-02-01 00:00:00 |  | True
 |  |  |  |  | Orton Timbrell | Y | 6 | 2022-02-15 00:00:00 |  | True
 |  |  |  |  | Orton Timbrell | N | 8 | 2022-03-15 00:00:00 |  | True
 |  |  |  |  | Orton Timbrell | Y | 8 | 2022-04-01 00:00:00 |  | True
 |  |  |  |  | Orton Timbrell | Y | 7 | 2022-07-15 00:00:00 |  | True
 |  |  |  |  | [NAME] | Y | 8 | 2022-02-01 00:00:00 |  | False
 |  |  |  |  | [NAME] | Y | 6 | 2022-02-15 00:00:00 |  | False
 |  |  |  |  | [NAME] | N | 8 | 2022-03-15 00:00:00 |  | False
 |  |  |  |  | [NAME] | Y | 8 | 2022-04-01 00:00:00 |  | False
 |  |  |  |  | [NAME] | Y | 7 | 2022-07-15 00:00:00 |  | False