24 Pay periodsThis 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.
