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.
The library's collections and services are available to all ISU students, faculty, and staff and Parks Library is open to the public.
