Calculating Paydays in a calendar year using Excel
In India salary is usually paid on the last working day of a month – the payday. It is slightly different in countries where payrolls are processed every 2 weeks. For eg. in US most companies pay salary on every 2nd Friday / Thursday.
We can calculate the paydays / payroll periods in excel with simple date formulas:
Calculating last working day of a month – Payday for monthly payroll processing
Put in other words, last working day of a month is nothing but 1 working day before first day of next month. So last working day of Jan 2008 is one working day before first day of Feb 2008; 2/1/2008.
Here is how you can find out payday for all months in a given calendar year :
Assuming months are in column B, from B3 to B14, last working day of first month can be found by WORKDAY() formula: =WORKDAY(B4,1)
. WORKDAY() excel formula calculates a future / past date by adding / subtracting any number of working days from it. Ex: =workday(today(),5)
will return the date of 5th working day from today.
Calculating Payroll Dates for Biweekly Salaries – Typical US Payroll Calendar Dates
Calculating US Payroll dates is slightly complicated. First we have to find the first Monday of the year. This begins the first payperiod of 26 payperiods in America. Then, each payday is exactly 12 days after the starting Monday.
Here is how we can find the first Monday of any given year: =if(weekday("1/1/2008")=2,"1/1/2008","1/1/2008"+9weekday("1/1/2008"))
, we are using weekday() function to find the day of week on 1/1/2008 and then add required number of days to it to get the First Monday of the year.
Once the first Monday is calculated, then finding the payday dates for each pay period is simple. First payment date is 12 days away from the Monday and subsequent pay dates are 14 days apart.
More on date / time: 10 tips on using, formatting date / time in excel.
 
 

Leave a Reply
Web Page Monitor using Google Docs  Visualization Challenge – How to show market share changes? 
9 Responses to “Calculating Paydays in a calendar year using Excel”
I am a huge fan of the workday and networkday formulas in Excel. I have impressed many folks with it’s power and it’s really simple to use.
@Tony.. you are right. Despite looking simple both networkdays and workday are extremely powerful and handy tools when it comes to processing dates.
How can I find my pay dates in the future? I get paid on February 1st this year, and every 2nd wednesday thereafter, so Feb 2012 is a 3 paycheque month. I’m wanting to find the next time I get paid in a 3 paycheque February, and I think that’s 2068…
Any expert help is appreciated.
@Geo
Lets say you have Feb 1 in cell B2
In B3 simply put =B2+14
Copy B3 down
You can format the cell using a custom Number Format; Ctrl 1; however you want
I utilized this formula to make a dynamic annual calendar at work, showing holidays and paydays. I found that the pay period gets messed up due to leap years. Does anyone have any idea why?
I have a problem. We pay our staff on the 28th of a given month. If the 28th falls on a weekend or holiday we pay on the last work day before it. Payroll needs to be run two working days before the pay day and so we need everyone’s hours in the day before that.
I tried to used the workday function [=WORKDAY(28th of month,3,Bank holidays)] to calculate when we need all hours in but it doesn’t always give the correct date. It gets thrown out by paydays which fall on the weekend. For example in July 2013 the 28th falls on a Sunday so we would pay on Friday 26th. Payroll should be run on Wednesday 24th and hours would need to be in on the 23rd. This formula returns the Wednesday for July but works for all other months in 2013.
Any ideas
I’ve worked it out actually. Using nested WORKDAY functions I effectively calculate the pay date, then when I will need to run payroll and finally when hours need to be submitted. Payroll has to be run on a weekday but I can ask for hours in on a Sunday which is why I have setup the formula as I have:
=WORKDAY(WORKDAY(28th of month+1,1,Holidays),2,Holidays)1
I have joint custody of my daughter. She arrives every other Sunday and stays til the following Sunday (exchange time is Sundays at 730 pm).
I want to generate a list of all the days I have my daughter so when someone asks me to do something in the future, I know whether or not I have my daughter on that date….I would generate a pdf from the list of dates for searching on my smartphone…
Any help greatly appreciated!
Bill
Excel 2010
The 1st Monday of the new year is Week 1 of 52.
I used this method to derive that
in Cell A1 obtain the current year using =YEAR(TODAY())
in any other cell, enter
=DATE(A1,1,8)WEEKDAY(DATE(A1,1,6))