Date formula Help!

[RLJ]

I need some help figuring out a set of formulas to display some dates. In the example below, Column B works since the Work Date is 11/128/2023. Column E does not work since the date is 1/11/2024. I need the dates in column H.

These dates are annual review dates and when in 2023, the dates all work since all is more than 2 months out from the Work Date of 11/18/2023. The dates in Column E do not work because some are less than 2 months out from the work date and some are more than 2 months out from the Work Date of 1/11/2024.

Since the Work Date is 1/11/2024 and 2 months is 3/11/2024, the 60 day value of 3/1/2025 is good, but the 90, 120, 150 & 180 dates are more than 2 months from the Work Date and I need to have the 2024 values vs the 2025 values. Column H is what I would like the formulas to produce.

Basically, I need the formulas to look at the 2 months from Work Date and if they are less, return the next years values and if they are greater, display the current year values.

Sample Date.xlsx
	A	B	C	D	E	F	G	H
1	Good			Bad			What I need
2	Work Date	11/18/2023		Work Date	1/11/2024		Work Date	1/11/2024
3	Next Year	1/1/2024		Next Year	1/1/2025		Next Year	1/1/2025
4	60 Days from YE	3/1/2024		60 Days from YE	3/1/2025		60 Days from YE	3/1/2025
5	90 Days from YE	4/1/2024		90 Days from YE	4/1/2025		90 Days from YE	4/1/2024
6	120 Days from YE	5/1/2024		120 Days from YE	5/1/2025		120 Days from YE	5/1/2024
7	150 Days from YE	6/1/2024		150 Days from YE	6/1/2025		150 Days from YE	6/1/2024
8	180 Days from YE	7/1/2024		180 Days from YE	7/1/2025		180 Days from YE	7/1/2024
9
10
11				Workdate + 2 months	3/11/2024
Sheet1

Cell Formulas
Range	Formula
H3,E3,B3	H3	=DATE(YEAR(H2),12,31)+1
H4,E4,B4	H4	=DATE(YEAR(H3),MONTH(H3)+2,DAY(1))
B5,E5	B5	=DATE(YEAR(B3),MONTH(B3)+3,DAY(1))
B6,E6	B6	=DATE(YEAR(B3),MONTH(B3)+4,DAY(1))
B7,E7	B7	=DATE(YEAR(B3),MONTH(B3)+5,DAY(1))
B8,E8	B8	=DATE(YEAR(B3),MONTH(B3)+6,DAY(1))
E11	E11	=DATE(YEAR($E$2),MONTH($E$2)+2,DAY($E$2))

Thank you for your help!

 

[Asbestos_Jen]

So YE= yearly event, and not Year End, as I was assuming.

Does this logical argument fit your situation, or is it something else?

If (workdate+2mos) > (YE+2mos) return (YE+2mos) otherwise return (YE+14 mos)

 

[Asbestos_Jen]

Have a look at these and see if NEW works for all the scenarios you might need. I can combine the helper columns into one formula once I know if it meets your needs.

NEW: IF (WD+2mos) > (TY+#mos) , return (NY+#mos) otherwise (TY+#mos)

2024-01-12_Workdate.xlsx
	D	E			H	I	J	K		M	N	O	P			S	T	U	V		X
1	Good Original				NEW		WD	18-Nov-23				Good				NEW 3		WD	12-Jan-24
2	Work Date - WD	18-Nov-23					NextYear	1-Jan-24				Work Date - WD	11-Jan-24					NextYear	1-Jan-25
3	Next Year (Jan. 1) - NY	1-Jan-24					#mos	NY+mos		TY+mos		Next Year (Jan. 1) - NY	1-Jan-25					#mos	NY+mos		TY+mos
4	ThisYear (Jan. 1) - TY	1-Jan-23										ThisYear (Jan. 1) - TY	1-Jan-24
5	60 Days from YE	1-Mar-24			1-Mar-24		2	1-Mar-24		1-Mar-23		60 Days from YE	1-Mar-25			1-Mar-25		2	1-Mar-25		1-Mar-24
6	90 Days from YE	1-Apr-24			1-Apr-24		3	1-Apr-24		1-Apr-23		90 Days from YE	1-Apr-24			1-Apr-24		3	1-Apr-25		1-Apr-24
7	120 Days from YE	1-May-24			1-May-24		4	1-May-24		1-May-23		120 Days from YE	1-May-24			1-May-24		4	1-May-25		1-May-24
8	150 Days from YE	1-Jun-24			1-Jun-24		5	1-Jun-24		1-Jun-23		150 Days from YE	1-Jun-24			1-Jun-24		5	1-Jun-25		1-Jun-24
9	180 Days from YE	1-Jul-24			1-Jul-24		6	1-Jul-24		1-Jul-23		180 Days from YE	1-Jul-24			1-Jul-24		6	1-Jul-25		1-Jul-24
10
11
12	Workdate + 2 months	18-Jan-24										Workdate + 2 months	12-Mar-24
Sheet3

Cell Formulas
Range	Formula
K1	K1	=E2
K2,P3,E3,V2	K2	=DATE(YEAR(K1),12,31)+1
E4,P4	E4	=DATE(YEAR(K1),1,1)
E5	E5	=DATE(YEAR(E3),MONTH(E3)+2,DAY(1))
S5:S9,H5:H9	H5	=IF(E$12>M5,K5,M5)
E6	E6	=DATE(YEAR(E3),MONTH(E3)+3,DAY(1))
E7	E7	=DATE(YEAR(E3),MONTH(E3)+4,DAY(1))
E8	E8	=DATE(YEAR(E3),MONTH(E3)+5,DAY(1))
E9	E9	=DATE(YEAR(E3),MONTH(E3)+6,DAY(1))
K5:K9	K5	=EDATE($K$2,$J5)
M5:M9	M5	=EDATE($E$4,J5)
P5	P5	=IF(MONTH(EDATE($P$3,2))>MONTH($P$12),DATE(YEAR(EDATE($P$3,2))-1,MONTH(EDATE($P$3,2)),1),EDATE($P$3,2))
P6	P6	=IF(MONTH(EDATE($P$3,3))>MONTH($P$12),DATE(YEAR(EDATE($P$3,3))-1,MONTH(EDATE($P$3,3)),1),EDATE($P$3,3))
P7	P7	=IF(MONTH(EDATE($P$3,4))>MONTH($P$12),DATE(YEAR(EDATE($P$3,4))-1,MONTH(EDATE($P$3,4)),1),EDATE($P$3,4))
P8	P8	=IF(MONTH(EDATE($P$3,5))>MONTH($P$12),DATE(YEAR(EDATE($P$3,5))-1,MONTH(EDATE($P$3,5)),1),EDATE($P$3,5))
P9	P9	=IF(MONTH(EDATE($P$3,6))>MONTH($P$12),DATE(YEAR(EDATE($P$3,6))-1,MONTH(EDATE($P$3,6)),1),EDATE($P$3,6))
V5:V9	V5	=EDATE($V$2,$U5)
X5	X5	=EDATE($P$4,$U5)
X6:X9	X6	=EDATE($P$4,U6)
E12	E12	=EDATE($K$1,2)
P12	P12	=EDATE($V$1,2)