Dynamically calculate first and last pay periods for year

I've been trying to figure out a calculation to determine the first and last pay periods in a year. Really I just need to figure out the last pay period for the year and I can calculate the first one for the next year by adding 14.

This will be going into a calendar where I need to gray out the last couple of days of the year in the last pay period as it will actually carry over to the next year.

I am looking for something like this, which gives me the start of the next pay period.

=(MOD(DATE(2010,1,8)-TODAY(),14)+TODAY())+1

Can you just change the 'TODAY()'s to 'DATE(2012,1,1) and then subtract your 14?

Pauley

Originally Posted by Pauleyb

Can you just change the 'TODAY()'s to 'DATE(2012,1,1) and then subtract your 14?

Pauley

This is actually kind of what I figured out. I wasn't wrapping my single braincell around it at first.

I actually meant to ask about this for VBA and this is what I came up with

((DateSerial(2010, 1, 8) - DateSerial(bYear + 1, 1, 1)) Mod 14) + DateSerial(bYear + 1, 1, 1)

bYear refers to a cell that contains the year, the +1 gives me next year.

Thanks for the help