Calculating Paydays in a calendar year using Excel

Posted on September 17th, 2008 in america , Learn Excel - 9 comments

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

payday-payroll-last-working-day-of-month-excel
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 :

last-working-day-of-month-spreadsheet-formula1Assuming 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

us-payroll-payday-calculation-26-pay-periods-excelCalculating 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.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

9 Responses to “Calculating Paydays in a calendar year using Excel”

  1. Tony says:

    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.

  2. Chandoo says:

    @Tony.. you are right. Despite looking simple both networkdays and workday are extremely powerful and handy tools when it comes to processing dates.

  3. Geo says:

    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.

  4. Hui... says:

    @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

  5. Michael says:

    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?

  6. JohnBoy says:

    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

  7. JohnBoy says:

    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

  8. William says:

    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

  9. Robert says:

    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))

Leave a Reply