Date calculations - Adding months.

[psycoperl]

I am working on a tracking spreadsheet to manage my time and leave. Since I get leave increments credited to my profile at the end of the month I was trying to figure out how to add one month to the date in the cell above. I saw the instructions on Microsoft's website suggesting the use of the EDATE function. However, it does not return the last day of the month. So if I go from August 31 it returns September 30 which is the correct value. However in the next instance it would return October 30 instead of October 31. It is even more drastic when going from February which only has 28 or 29 days, it now constantly returns either the 28 or 29th of the month.

What formula can I use to return the LAST day of each month?

Leave_Use_Plan_FY2021.xlsx
	AB	AC
1	SICK LEAVE	Leave Type
2	Monday, August 31, 2020	SL
3	Wednesday, September 30, 2020	SL
4	Friday, October 30, 2020	SL
5	Monday, November 30, 2020	SL
6	Wednesday, December 30, 2020	SL
7	Saturday, January 30, 2021	SL
8	Sunday, February 28, 2021	SL
9	Sunday, March 28, 2021	SL
10	Wednesday, April 28, 2021	SL
11	Friday, May 28, 2021	SL
12	Monday, June 28, 2021	SL
13	Wednesday, July 28, 2021	SL
14	Saturday, August 28, 2021	SL
15	Tuesday, September 28, 2021	SL
16	Thursday, October 28, 2021	SL
17	Sunday, November 28, 2021	SL
18	Tuesday, December 28, 2021	SL
19	Friday, January 28, 2022	SL
20	Monday, February 28, 2022	SL
Sheet1

 

[Toadstool]

Hi Psycoperl,

You can use EOMONTH

Book4
	AB	AC
1	SICK LEAVE	Leave Type
2	31-Aug-20	SL
3	30-Sep-20	SL
4	31-Oct-20	SL
5	30-Nov-20	SL
6	31-Dec-20	SL
7	31-Jan-21	SL
8	28-Feb-21	SL
9	31-Mar-21	SL
Sheet1

Cell Formulas
Range	Formula
AB3:AB9	AB3	=EOMONTH(AB2,1)

 

[psycoperl]

Hi Psycoperl,

You can use EOMONTH

Book4
	AB	AC
1	SICK LEAVE	Leave Type
2	31-Aug-20	SL
3	30-Sep-20	SL
4	31-Oct-20	SL
5	30-Nov-20	SL
6	31-Dec-20	SL
7	31-Jan-21	SL
8	28-Feb-21	SL
9	31-Mar-21	SL
Sheet1

Cell Formulas
Range	Formula
AB3:AB9	AB3	=EOMONTH(AB2,1)

Thanks

 

[Peter_SSs]

It may well be that the suggested formula approach is what you want, but in case it is of use to you, there are a couple of pretty quick manual ways to get such a list too.

1. Enter the first two dates manually (31 Aug and 30 Sep in your example) -> select both of those cells and drag the Fill Handle down as far as you want

2. Enter the first date only then either

2a. Select that cell and as many blank cells below as you want then Home ribbon tab --> Fill drop down -> Series ... -> Fill like this then OK

​

or​

2b. Select the first date cell only -> complete the Fill Series dialog like this​

​