change date

[Lauren3077]

I have a column of dates. They will all be different every month, but for last month range from 3/1/2021 to 3/31/2021. I need it to change the date to the last day of the month four months from the date. So, all of March's dates will need to change to 7/31/2021 but I need a formula that will change April's dates to August 31 and May's to September 30 and so on. It's a monthly spreadsheet. It also needs to start in D2 but go down as far as there is information, which is different every month. I saved this one until the end because I have no idea where to even start.

 

[jasonb75]

but I need a formula that will change April's dates to August 31 and May's to September 30 and so on.

That's what EOMONTH is for

=EOMONTH(D2,4)

The digit at the end is the number of months to add, use 0 if you need the end of the current month.

 

[jtakw]

Hi,

Use this:

Book3.xlsx
	C	D
1	3/1/2021	7/31/2021
2	3/15/2021	7/31/2021
3	4/5/2021	8/31/2021
4	5/25/2021	9/30/2021
Sheet917

Cell Formulas
Range	Formula
D1:D4	D1	=EOMONTH(C1,4)

 

[Lauren3077]

Oh man, that was so easy. Thank you guys so much for your help.

 

[jasonb75]

that was so easy

Excel is always the same. If you think it will be difficult, it will be easy. If you think it will be easy, it will be impossible.