It’s the time of year that young, energetic college students are being interviewed for summer internships. Today we selected our first summer hire and began the seemingly straightforward process of preparing the employment paperwork. The full packet grows heavier each year with the employee handbook, policy statements, a multitude of governmental forms, emergency contact form, and so on.
There is one part of the process that I find more than a little bothersome. The employment letter specifies the first day of employment and explains to the new employee the bi-weekly pay periods and the date that the first paycheck will be issued. Usually I pull up an electronic or printed calendar and go through a lot of manual gyrations, circling the paydays and counting back through the paycycle. Today I decided to spend a bit of time in Excel to automate this process.
Date Math becomes a “Calendar Calculator”
Date math refers to several functions in Excel which return the number of days between two dates or allows days to be added to or subtracted from a particular date. Our payroll is on a bi-weekly cycle with a one week delay. Each pay period begins on a Sunday and ends on a Saturday. Paychecks are issued on Friday. Using the month of March, if the pay period starts on Sunday, March 2, it will end 14 days later on March 15. The processing will occur in the following week and the paycheck for that period will be issued on Friday, March 21.
My objective is to enter a new employee’s start day and have formulas calculate his or her first payday, the first date of the pay period, and the last date of the pay period. To begin I pick a random payday in the past – January 11, 2013. We’ll name this ORIG_PAYDAY in the formula. It could have been any prior payday as all future paydays will always be some multiple of 14 from any prior payday. Next the employee’s start date (START_DATE) is entered in Cell B2.
The formula build begins with determining the number of pay periods between the ORIG_PAYDAY and the START_DATE.
=INT((START_DATE – ORIG_PAYDAY)/14).
The INT() function rounds the result down to the whole integer, so 30.2142 becomes 30. Thus, the new employee’s first payday will be either the 31st or 32nd payday after our arbitrary ORIG_PAYDAY.
Since it will be infrequent that a start date will fall precisely on the day of a new pay period, we’ll also need the remainder, i.e., the number of extra days beyond the 30 pay periods. That function is MOD((START_DATE – ORIG_PAYDAY),14), yielding in this case: 3. The remainder for each possible start date is shown in red below:
Note that all of the possible start dates with a remainder of 8 or less will be within the 2nd week of the pay period and will be paid on March 21, the 31st pay period. However, any start date with a remainder of 9 or more will fall in the processing week which is actually the 1st week of the next pay period and will be paid on April 4, the 32nd pay period.
Putting it altogether using date math, we add to the original payday the product of pay periods between the start date and original payday and plus one times 14 days.
The final calculations are easy-peasy. Subtracting 19 from the first payday returns the first day of the pay period and subtracting 6 returns the last day of the period.
Breaking long formulas down into their component parts can help make them less intimidating. Creating this formula by building those components one at a time took a good thirty minutes, a fair amount longer than my usual manual calendar counting. However, now I have the worksheet saved in the same folder in which I store the employment letter templates and in the future, it will take all of 15 seconds to open the file, type in the employee’s start date, and receive all three relevant dates.
If you found this post helpful, you may subscribe to receive more great tips, tricks, and templates by email.