Formula to insert a specific date within a column of quarters

[hellopello]

I have a column of quarter-end dates and would like to insert a particular date in this column. I'm trying to use IF formulas, but for the life of me, cannot get the formula to work.

For example, the particular date I have is 5 Aug 2022. My column date starts from 31 Dec 2021

Column A
Date
31 Dec 2021
31 Mar 2022 (using EOMONTH(Cell,3) formula)
30 Jun 2022
5 Aug 2022 <-- i want to insert my 5 Aug 2022 date here
30 Sep 2022 <-- i want to continue my quarter end dates here
31 Dec 2022
31 Mar 2023
....

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this does what you want.

22 08 05.xlsm
	A	B	C
1	Date		Special Date
2	31-Dec-21		5/08/2022
3	31-Mar-22
4	30-Jun-22
5	5-Aug-22
6	30-Sep-22
7	30-Dec-22
8	30-Mar-23
9	30-Jun-23
10	30-Sep-23
Dates

Cell Formulas
Range	Formula
A3:A10	A3	=IF(A2=C$2,EDATE(A1,3),IF(AND(A2<C$2,EDATE(A2,3)>C$2),C$2,EDATE(A2,3)))

 

[Peter_SSs]

Hello, I have noticed that you have marked my post as the solution (glad it worked for you), but you have still not provided your version details in your profile. Please consider doing so as it will assist helpers provide the best advice for any future questions that you may have.