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.
What is happening?
Use the chart below to figure starting month:
|Starting Month||IF() VALUE 1||IF() VALUE 2|
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:
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.