# Excel question: When months have 31 days
**URL:** https://boards.straightdope.com/t/excel-question-when-months-have-31-days/379568
**Category:** Factual Questions
**Created:** [November 8, 2006, 4:33pm UTC](https://boards.straightdope.com/t/excel-question-when-months-have-31-days/379568 "2006-11-08T16:33:26Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Carol\_the\_Impaler](https://boards.straightdope.com/u/Carol_the_Impaler)
#### Post date: [November 8, 2006, 4:33pm UTC](https://boards.straightdope.com/t/excel-question-when-months-have-31-days/379568/1 "2006-11-08T16:33:26Z")
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?
---
### Author:  [@Ximenean](https://boards.straightdope.com/u/Ximenean)
#### Post date: [November 8, 2006, 4:54pm UTC](https://boards.straightdope.com/t/excel-question-when-months-have-31-days/379568/2 "2006-11-08T16:54:35Z")
Try =DATE(YEAR($C4),MONTH($C4)+F8+1,1)-1
i.e. the day before the first day of the following month.
---
### Author:  [@Carol\_the\_Impaler](https://boards.straightdope.com/u/Carol_the_Impaler)
#### Post date: [November 8, 2006, 5:42pm UTC](https://boards.straightdope.com/t/excel-question-when-months-have-31-days/379568/3 "2006-11-08T17:42:49Z")
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.
---
### Author:  [@Ximenean](https://boards.straightdope.com/u/Ximenean)
#### Post date: [November 8, 2006, 5:52pm UTC](https://boards.straightdope.com/t/excel-question-when-months-have-31-days/379568/4 "2006-11-08T17:52:07Z")
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().
---
### Author:  [@Carol\_the\_Impaler](https://boards.straightdope.com/u/Carol_the_Impaler)
#### Post date: [November 8, 2006, 6:04pm UTC](https://boards.straightdope.com/t/excel-question-when-months-have-31-days/379568/5 "2006-11-08T18:04:59Z")
That’s perfect, **Usram**. Thanks again. It worked!