CODEWITHSUNDEEP
excel
traveler84
traveler84

Reputation: 474

Excel and pay days

Cell F2 holds the year. I'm trying to figure out how to dynamically change the days for the pay periods for a given year. We always have 26, and the next one is 12/12. But if I type a new year in f2, I wanted it to show all the fridays I am paid that year.

I have 26 cells setup for it.

Maybe a formula that uses the the first date in jan that we are paid and add f2 - 2015 with a date formula?

= date(2015,1,9) + year(f2 - 2015) but next year it is on a different day.

Upvotes: 0

Views: 100

Answers (1)

barry houdini
barry houdini

Reputation: 46331

If the year is in F2 then you can get the first pay day of any future year with this formula

=CEILING(DATE(F2,1,1)-DATE(2014,12,12),14)+DATE(2014,12,12)

Then just add 14 to that for subsequent paydays in the year, so if you have that formula in F4 then F5 should be just

=F4+14

format in required date format and copy that formula down the column as far as required

Upvotes: 2

Related Questions