Date Formula to get the July after a certain date

**twslisa** · 08-16-2012, 10:50 PM

Hi! I'm working on a spreadsheet that will calculate the retirement benefit for people on our pension plan. One of the calculations is based on the number of months between the date of retirement and the next July 1 (fiscal year start). I've tried =DATE(YEAR(Inputs!B12),CEILING(MONTH(Inputs!B12),6)+1,1), and it works great if the retirement date is between January and June, but not so fine if you retired between July and December. =DATE(YEAR(Inputs!B12)*1,13,1) works great to get me to the first January after retirement consistently, but I can't figure out what to change to make it July.

Can anyone help?

Thanks!

**JBeaucaire** · 08-16-2012, 11:02 PM

How about:

=DATE(YEAR(I2), CHOOSE(MONTH(I2),7,7,7,7,7,7,19,19,19,19,19,19), 1)

Into this:

=DATEDIF(I2, DATE(YEAR(I2), CHOOSE(MONTH(I2),7,7,7,7,7,7,19,19,19,19,19,19), 1), "m")

**twslisa** · 08-16-2012, 11:08 PM

Bingo! You Rock! I'm not even going to pretend to understand the logic, so long as it works!

**JBeaucaire** · 08-16-2012, 11:49 PM

CHOOSE takes the number from the first parameter and returns that item from the "list" that follows. So:

CHoose(4, cat,dog,bird,lizard,hamster,frog)

....would return "lizard" since it's the 4th thing in the list. OK?

So we use CHOOSE to spot the month number of your cell I2 date. Then it returns either THIS july (month 7) or NEXT July (month 19) into that month field.

So it takes the year from I2 and then puts in either 7 or 19 for the month, then 1 for the day. Months after 12 automatically increment the year and roll back to under 12.

DATEDIF is used to calculate the difference between two dates. The first date is your input cell, I2, the second is the next July, then "m" indicates give us the answer in months.

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.