Early on in my career, I was tasked with creating a better Timesheet for tracking how an auditor is spending their time on clients and projects. Little did I know that this was going to give me a tool that I would use for the next twenty years of my work life.
One drawback…while I pay close attention to how I use my time at work, I rarely think much about how I use my vacation. As a result, I usually just take a long weekend here and there to visit relatives. This does little to rest, recharge and refocus. I decided to abandon my Timesheet spreadsheet and instead build a brand new spreadsheet to track paid holidays and paid time off. Here’s what I learned:
Formulas with Dates are Complicated
I rarely have to resort to pen and paper when creating a formula in Excel. Almost never do I have to Google the answer. When it comes to calculating what day is Easter or Presidents’ Day, I resorted to Google. I still don’t understand the formula for Easter.
For the Holidays, I relied heavily on the Weekday() function as you had to know what day of the week the holiday was falling on. For example, Thanksgiving is always on a Thursday. While Christmas is always on December 25th, if the 25th is on a Sunday, you usually get the following Monday off.
For the Weekday() function, you enter the date, and it returns a number between 1 and 7 representing the day of the week with 1 being Sunday.
A Formula can’t solve every Problem
When I was working on the Paid Time Off sheet, I was pretty happy how I was able to create a formula that would look at today’s date, my starting date, and how much time I have used, and automatically calculate how much vacation time I have remaining.
With my sick time, there is a cap of 24 days that you can accumulate. Possibly there is a formula, but I gave up after about an hour and added a couple of columns to calculate the sick time on the side. A functional spreadsheet is better than the spreadsheet than the incomplete Perfect spreadsheet.
Useful Links
- Download Spreadsheet: https://jctaccounting.com/wp-content/uploads/2024/03/PTO_and_Holiday_Tracker.xlsx
- Full Focus Planner: https://fullfocusstore.com/pages/planner
- Subscribe to my Blog: https://jctaccounting.com/subscribe