Excel question: When months have 31 days

[Carol the Impaler]

I'm creating a spreadsheet that calculates data based on the current month and then 3 months out, 6 months out, 9 months out and 12 months out.

What I want to do is to write a formula so that my boss can enter in the date for the first day of the current month in the "Start-Date Current Month" cell and the last day of the month in the "End-Date Current Month" cell and have the other Start and End dates automatically fill in.

The Start-Dates were easy, as every month's first day is "1".

But how do I get the End Dates to fill as the 30th when appropriate or the 31st when appropriate?

This is the formula I wrote to result in the date 3 months/6 months hence, etc:

=DATE(YEAR($C4),MONTH($C4)+F8,DAY($C4))

I'm assuming that I need to do an IF function or a LOOKUP function, but that's a bit beyond me.

How do I write a formula that will add 3/6/9/12 months to the current month entered and result in the correct last day of the month?

[Ximenean]

Try =DATE(YEAR($C4),MONTH($C4)+F8+1,1)-1

i.e. the day before the first day of the following month.

[Carol the Impaler]

Thanks, Usram.

I'm getting the #NUM! error when I use this formula.

Could you be so kind and explain in words what I'm asking Excel to do? I think I can fix the formula, but I'm only 80% certain I understand what the formula is supposed to return.

[Ximenean]

Well, the MONTH($C4)+F8+1 part takes whatever date is in C4 and extracts the month number, adds the number in F8 to that, and then adds one i.e it's pointing to the month *after* the 3rd/6th/9th/12th month. And the whole DATE() function calculates the date of the first day of that month. And the -1 at the end subtracts one from that date, i.e. gives you the previous day, the last day of the 3rd/6th/9th/12th month.

I also see that if you install something called the Analysis ToolPak there's an end-of-month function, EOMONTH().

[Carol the Impaler]

That's perfect, Usram. Thanks again. It worked!