We help IT Professionals succeed at work.
Troubleshooting Question

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

on
119 Views
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).

Definitions:
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

TimeCard-2021-03-14.xlsm
Comment
Watch Question
Keep everyone healthy; Wear a mask - Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2020
Commented:
This problem has been solved!
###### 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

Deciding to stick with EE.

Mohamed Asif

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

Carl Webster
CTP, Sr Infrastructure Consultant