Spreadsheet: 880997d1728900843-attendance-tracking-separate-worksheets-rolling-12-months-and-consecutive-day-calculations-2024-tracker-copy1
Sheet: 2024 PTO

PTO Tracker |  | Company name
 |  | My business |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 2024
 |  | Year
 |  | 2024 |  |  |  | f4 | c13 | U, O, T1, T2
 |  |  |  |  |  | k6:aq6 | i13:zz18 | calendar body
 | Today |  |  |  |  | k5:[NAME] | i11:zz11 | Months row
 | Roll off date |  |  |  |  | i1 | c6 | todays date
 | Employee AK |  |  |  | should be |  |  | 2023-10-01 00:00:00 |  |  |  |  |  |  |  | 2023-11-01 00:00:00 |  |  |  |  |  |  |  | 2023-12-01 00:00:00 |  |  |  |  |  |  |  | 2024-01-01 00:00:00 |  |  |  |  |  |  |  | 2024-02-01 00:00:00 |  |  |  |  |  |  |  | 2024-03-01 00:00:00 |  |  |  |  |  |  |  | 2024-04-01 00:00:00 |  |  |  |  |  |  |  | 2024-05-01 00:00:00 |  |  |  |  |  |  |  | 2024-06-01 00:00:00 |  |  |  |  |  |  |  | 2024-07-01 00:00:00 |  |  |  |  |  |  |  | 2024-08-01 00:00:00 |  |  |  |  |  |  |  | 2024-09-01 00:00:00 |  |  |  |  |  |  |  | 2024-10-01 00:00:00 |  |  |  |  |  |  |  | 2024-11-01 00:00:00 |  |  |  |  |  |  |  | 2024-12-01 00:00:00
 |  | Total |  |  |  |  |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa
 |  | U |  |  | 6 |  |  |  |  |  |  |  | t1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | T1
 |  | O |  |  | 2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | t1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1900-01-06 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | t1 |  |  |  |  |  |  |  | U
 |  | T1 |  |  | 1.67 |  |  |  |  |  |  | 1900-01-19 00:00:00 |  |  |  |  |  |  |  |  | t1 |  |  |  |  |  |  | u |  |  |  |  |  |  |  |  |  |  |  |  | 1900-01-12 00:00:00 |  | 1900-01-14 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | t2
 |  | T2 |  |  | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | U |  |  |  |  |  |  |  | 1900-01-19 00:00:00 |  |  | U | 1900-01-23 00:00:00 |  |  |  |  |  |  | 1900-01-21 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | t1 | o | o |  |  |  |  |  |  | 1900-01-24 00:00:00
 |  |  |  |  |  |  |  |  | u | u |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1900-01-26 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | U | u
 |  | "U" for employee AK (D10) should be 6.00, due to consecutive days & rolling 12 months
 | Employee JS |  |  |  | should be |  |  | 2023-10-01 00:00:00 |  |  |  |  |  |  |  | 2023-11-01 00:00:00 |  |  |  |  |  |  |  | 2023-12-01 00:00:00 |  |  |  |  |  |  |  | 2024-01-01 00:00:00 |  |  |  |  |  |  |  | 2024-02-01 00:00:00 |  |  |  |  |  |  |  | 2024-03-01 00:00:00 |  |  |  |  |  |  |  | 2024-04-01 00:00:00 |  |  |  |  |  |  |  | 2024-05-01 00:00:00 |  |  |  |  |  |  |  | 2024-06-01 00:00:00 |  |  |  |  |  |  |  | 2024-07-01 00:00:00 |  |  |  |  |  |  |  | 2024-08-01 00:00:00 |  |  |  |  |  |  |  | 2024-09-01 00:00:00 |  |  |  |  |  |  |  | 2024-10-01 00:00:00 |  |  |  |  |  |  |  | 2024-11-01 00:00:00 |  |  |  |  |  |  |  | 2024-12-01 00:00:00
 |  | Total |  |  |  |  |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa |  | Su | Mo | [NAME] | We | Th | Fr | Sa
 |  | U |  |  | 3 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | u | 1900-01-03 00:00:00
 |  | O |  |  | 1 |  |  |  |  |  |  |  | t1 |  |  |  |  |  | t2 |  |  |  |  |  |  |  |  | u | u |  |  |  |  |  |  |  |  |  |  |  |  | 1900-01-06 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1900-01-05 00:00:00 |  |  |  |  |  |  |  | t2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1900-01-07 00:00:00
 |  | T1 |  |  | 0.67 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | u |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1900-01-12 00:00:00 |  | 1900-01-14 00:00:00 | t2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | u |  |  |  |  |  |  |  |  |  |  |  |  | t1 |  | t1
 |  | T2 |  |  | 3 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1900-01-22 00:00:00 |  |  |  |  |  |  |  | 1900-01-19 00:00:00 |  |  | 1900-01-22 00:00:00 | 1900-01-23 00:00:00 |  |  |  |  |  |  | 1900-01-21 00:00:00 |  |  |  |  | u |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1900-01-24 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1900-01-26 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1900-01-29 00:00:00 | o
 |  | "U" for employee JS (D20) should be 3.00, due to consecutive days and rolling 12 months
 |  |  |  | SUMPRODUCT(--((FREQUENCY(IF(C$13=$I13:$ZZ18,IF($I$11:$ZZ$11>=EDATE($C$6,-12),IF($I$11:$ZZ$11<=$I$1,COLUMN($I13:$ZZ18)))),IF(C$13<>$I13:$ZZ18,IF($I$11:$ZZ$11>=EDATE($I$1,-12),IF($I$11:$ZZ$11<=$C$6,COLUMN($I13:$ZZ18)))))>0)))