fbpx
Search
Close this search box.

Calculating Paydays in a calendar year using Excel

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

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

  10. Heidi says:

    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

  11. Heidi says:

    btw, our new pay period according to our Payroll processor is Sep 12 to Sep 26, 2015

    • Hui... says:

      @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

  12. […] 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. […]

  13. Shanu Singh says:

    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

  14. John says:

    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.

  15. John says:

    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.

Leave a Reply