troubleshooting Question

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

Avatar of phoffric
phoffric asked on
Microsoft OfficeMicrosoft ExcelVBA
111 Comments2 Solutions608 ViewsLast Modified:
Ref: https://www.experts-exchange.com/questions/29186184/How-to-remove-error-prone-manual-operations-from-Time-and-Activity-spreadsheet.html 

I am entering some formulas manually, and would like to automate them, if possible.

Attached is a spreadsheet that Martin Liss wrote that mirrored my original layout and the formulas that I wrote to track my time against two online timecards. My original two timecards (one for the Prime and one for my company) were broken into two Pay Periods per month. The first Pay Period ran from the 1st of the month to the 15th. The 2nd Pay Period ran from the 16th to the last day of the month. (Pay Periods had a varying number of days depending on how many weekends and holidays were in the Pay Period.) What I used to do manually in figuring out the number of days in a Pay Period is now done automatically using formulas.

The prime's timecard gives a cumulative sum for the Pay Period, as you would expect. A holiday in a Pay Period always results in different cumulative hours between these two online timecards.

But, my company just switched in July their payroll to another payment processor, and the new Timecard always starts on the 1st of the month and ends on the last day of the month. In between are weekly totals of my hours. The week runs from Sunday to Saturday except possibly for the 1st and last week of the month!

I was tracking the hours in my Excel doc just fine until this recent change. Looking at the two online timecards, it is not so easy to make sure that the cumulative sums are correct given how staggered each total is.

Looking ahead at August, I see 6 weeks. (I know - hard to believe).
Week 1: 8/01 - 8/1 (week always ends on a Saturday)
Week 2: 8/02 - 8/8
Week 3: 8/09 - 8/15
Week 4: 8/16 - 8/22
Week 5: 8/23 - 8/29
Week 6: 8/30 - 8/31

This new timecard gives a cumulative sum for each week, but as you can see, a week does not always have 7 days in it.

I have attached my current Excel doc that I am using. Towards the bottom are yellow highlights which shows Weekly totals, and also a monthly total. I enter the formulas manually. Oops, If Saturday is the last day of the month, I have to move things around a bit to avoid overwriting the current computed information. These additions I made are helping me have some level of confidence that I am entering the times correctly in the two timecards. (Keep in mind that if there is a holiday, then I put 8 hours in for my company's timecard, and leave the holiday blank in the prime's timecard. Just another wrinkle to immediately say all is correct - not always easy to add/subtract 8 hours or more if using Paid Time Off to reconcile the books.)

Somehow, I need to be able to track both time cards a little better. What I did in those yellow highlighted cells is my first step. Not perfect because I do not really reconcile holidays and Paid Time Off with both online timecards. The spreadsheet more closely reflects the prime's online timecard.

29186184e.xlsm
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 111 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 111 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros