Skip to Main Content

Microsoft Tips and Codes

Codes, tips and tricks found over years of experience using Office.

24 Pay Periods

Red Diamond 24 Pay periods

This script will calculate a date cell on a 24 pay period beginning July 1 of the given year, and a pay period on the 15th and last day of the month. Place this script in cell B2 with a date in A2 to see the result.

=MOD(MONTH(A2)*2+IF(DAY(A2)>15,11,10),24)+1

What is happening?

Use the chart below to figure starting month:

Starting Month IF() VALUE 1 IF() VALUE 2
January 23 22
February 21 20
March 19 18
April 17 16
May 15 14
June 13 12
July 11 10
August 9 8
September 7 6
October 5 4
November 3 2
December 1 0

And then pick a cell for the formula to go, usually directly beside the [DATE CELL] you want to use.

Place those values in the IF statement as follows:

=MOD(MONTH([DATE CELL])*2+IF(DAY([DATE CELL])>15,[IF VALUE 1],[IF VALUE 2]),24)+1

So, a starting date in December with the date 12/1/2009 in A2 and this formula in B2 would look like this:

=MOD(MONTH(A2)*2+IF(DAY(A2)>15,1,0),24)+1

Try it! Fill the A column with a year of dates, and then drag the B2 formula to the end. You do this by selecting cell B2 and then double-clicking the small back square in the lower right-hand corner of the cell. The formula will auto-fill to the end of the dates in Column A.

Subject Guide

Profile Photo
Megan O'Donnell
she / her
Contact:
150 Parks Library
515-294-1670