How many pay periods are left in 2022

You can watch a video tutorial here.

In this approach, we take the total number of days in the year and divide it by the number of days between payments. We then use the DATE() and YEAR() functions with date arithmetic to arrive at the number of pay periods left in the year, based on the current date.

  1. DATE() function: this takes a value each for the year, month and day, and combines them to create a date
    1. Syntax: DATE(year, month, day)
      1. year: the year of the date as 4 digits
      2. month: the month of the date, from 1 to 12
      3. day: the day of the date, from 1 to 31
  2. YEAR() function: this extracts the year from a date
    1. Syntax: YEAR(date)
      1. date: any date that has been formatted as a date

We will make all the formulas use cell references so that the pay period can be changed and the values will be updated.

A pay period is the frequency at which employees get paid. It could be weekly, bi-weekly or monthly, depending on the policy of the company. It is simple to find how many pay periods are left in the year using Excel.  As an HR Manager, you may want to find out how many pay periods are left in the year so that you can forecast the payroll requirements to help with the budget.

Step 1 – Set up the parameters

– Enter the date for the start of the year
– Type in the TODAY() function to return today’s date:
– Specify the number of days between payments

Step 2 – Create the formula

– Type the formula using cell references:
= =((DATE(YEAR(Date),12,31)-Date)/ $Number of days between payments$)+1
– DATE(YEAR(Date),12,31 returns the last day of the current year
– 1 is added at the end to include the last day as well
– The ‘Number of days between payments’ is made constant as the formula is going to be copied
– Press Enter

Step 3 – Enhance the formula to round off the number

– If you need to use a whole number and not a decimal, enhance the formula using the ROUND() function:
= ROUND(formula,0)
– The number will be rounded up or down to the nearest whole number

Step 4 – Copy the formula

– Select the cell with the formula and press Ctrl+C
– Select the cell below and press Enter
– More dates can be added to the date column and the formula can be copied down accordingly
– The number of days between payments can be changed and the numbers will get updated e.g. enter 14 for fortnightly payments