We help IT Professionals succeed at work.
Get Started
Troubleshooting Question

Part 3: Tracking timecard hours automatically in Excel against two different online Timecard formats

Last Modified: 2021-04-01
My previous question worked fine: Tracking timecard hours automatically in Excel against two different online Timecard formats. That is, until my company changed payroll providers again (three times in one year). The new one is much easier to handle, so my Excel time tracking system is much easier to manage. I have attached a file with the macros from the other question. But since I have a new timecard system, the format has changed.

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
Change G1 title to blue "PTO & Pay Hrs Left in Pay Period".
Change H1 title  to blue  "Holidays".
Change D2 formula (see below).
Change I18 formula (see below)
Change G4:G17 (see below).
Change K2 from VBA to 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

PTO Information:
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

3rd PP (Feb-01 to Feb-15):
I39: Formula: PTO Carry Over. =I2
J39: Formula: PTO Accrued. =10+J20-K20
Watch Question
Keep everyone healthy; Wear a mask - Social distance - Don't touch your face - Wash your hands for 20 seconds
Most Valuable Expert 2017
Distinguished Expert 2020
This problem has been solved!
Unlock 1 Answer and 133 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE