Excel question: When months have 31 days

Factual Questions
1

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?

2

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

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

3

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.

4

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().

5

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