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 pay-period of 26 pay-periods 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"+9-weekday("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.
16 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))
I am trying to configure which payroll dates are included for each payroll for a SEMIMONTHLY payroll. I used a formula based on the first payroll to add 16 to the next day after the last day of the payroll, but this month they are saying there are 15 days. I am assuming it has to do with one month having 31 days, and others having 30, and February which has 28 or 29 days.
This is for hourly employees
Is there a formula to help me configure a list of each payroll for 2015 and 2016?
Our last pay period was Aug 27 to Sep 11, 2015
btw, our new pay period according to our Payroll processor is Sep 12 to Sep 26, 2015
@Heidi
Are you sure your pay periods aren't a standard 2 weeks
ie: Start on Sat and Finish on Fri 2 weeks later
Sep 12 to Sep 25, 2015 is 2 weeks
The next period would be Sep 26 to 10 Oct
[…] As a rule of thumb, hourly employees should have their wages calculated before salaried employees. Hours should be grouped in accordance with the pay frequency: weekly, biweekly, semimonthly, or monthly. Excel can be used to calculate paydays in a calendar year. […]
Hi Guys,
Can somebody explain how to calculate thru formula?
I am trying to calculate salary between two dates e.g. From 3-Apr-2017 To 31-Mar-2018 and salary 50000/p.m.
I had used formula for dates calculation : =(DATE(YEAR(B21),MONTH(B21),DAY(B21))-DATE(YEAR(A21),MONTH(A21),DAY(A21)))/(365.25/12)
Got the period : 11.89
Now next used formula for salary : 50000*11.89 = 594661.191
There is different from manual calculation Rs. 2005.809035.
Manual Calculation
Fist counted Months from 1-May-2017 to 31-Mar-2018 so there are 11 months and 2nd counted days from 03-Apr-2017 to 30-Apr-2017 there are 28 days.
For Salary calculation
=50000*11=550000
=50000*28/30=46666.66667
Total Salary uptoo 31-Mar-2018
=550000+46667=596667
Thanks
Shanu
I have a tough one here, I have an excel calendar, with a cell that is labeled "Next Pay Day Date 2021".
So it's 1 cell. How can I get this 1 cell to update automatically with the new pay date?
So today is 7/1/2021, that cell should display 7/8/2021 because that is the next pay day.
Once the calendar reaches 7/9/2021, that cell should auto update to show 7/23/2021, reflecting the next pay day, 2 weeks away. It should continue to display 7/23/2021, until 7/24/2021 and it should update to 8/6.
Any help is appreciated, thank you.
Another tough one,
I would love to have a cell on my excel calendar that would display vacation accumulation.
I accumulate vacation at a rate of 3.076923077 hours per pay period.
I am paid every 2 weeks. (today is 7/1/22021, next pay day is 7/9/2021)
How can I enter a string that will add 3.076923077 hours to a cell every 2 weeks?
Again, any help is appreciated, thank you.