Here is what the new format looks like now.<<EDIT>> 03-16 8:35am
Change J20 to formula: =J2-K2
Add 3rd PP: J39 formula: =10+J20-K20 <<EDIT>> 03-15 7:50pm
title to blue "PTO & Pay Hrs Left in Pay Period
title to blue "Holidays
Change D2 formula
formula (see below)
A2: VBA: The number of workdays (M-F) in a Pay Period (PP). Originally, this number was computed to exclude holidays, so A2 was originally 10 (since Jan-1 is New Year's Day), but now it includes all workdays, so it is now 11.
First Pay Period (Jan-1 to Jan-31):
Orange Billable Information:
C2: VBA: The number of remaining work-days (M-F). In the picture, there are two days left in the PP (A16:A17). This value is
D2: Formula: The target number of Billable Hours to reach by the end of the PP. = 8*A2 - SUM(G3:H17)
D3: VBA: Green filled holiday. D3 is set to 0. H3 is set to 8.
D4:D17: I enter billable hours here.
D18: Formula: The sum of the Billable hours =SUM(D3:D17)
E18: Formula: Billable Hours left in PP: =D2 - D18
F18: Formula: Average Billable Hours/Day left: =E18/C2
G4:G17: I enter PTO hours here.
Blue Total Information:
H2: Formula: The target total hours for this PP (Billable and non-Billable): =8*A2
G18: Formula: The Total Hours left in PP (Billable and non-Billable): =H2-H18
H18: Formula: Current sum of entered Billable and non-Billable hours for this PP. =D18 + I18
I18: Formula: Current sum of entered non-Billable hours (Holidays and PTO) for this PP: =SUM(G3:H17) see <<EDIT>> 03-15 7:50pm
3rd PP (Feb-01 to Feb-15)
I2: PTO Carry Over. Manually entered at start of year.
J2: VBA: PTO Accrued. I get 10 hours per month. After one year (12 months), I get 10*12 = 120 hours, which is 3 weeks @40 hours/week.
K2: Formula: PTO Used in Pay Period: This is =SUM(G3:G17) = 4
L2: Formula: PTO Left for the year. =I2+J2-K2
2nd PP (Jan-16 to Jan-31): Here are two fields that require special attention. All other corresponding fields have same meaning as in first PP.
I20: Formula: PTO Carry Over. =I2
J20: Formula: PTO Accrued. =J2-K2
: PTO Carry Over. =I2J39
: PTO Accrued. =10+J20-K20 TimeCard-2021-03-14.xlsm