Formula with date

[ACQ]

Dear friends,

I have to generate a table of data with dates, and return if is over 3 months from today ie. today is 07 JAN 2022, if data is in APR 2022 then return the date, if data is MAY 2022 and beyond, and return "renewed".

I had input formula =IF(C3>EOMONTH(TODAY(),3),"Renewed",B3), but all data return "renewed"

Pls help.

Thanks
AC

Book2
	A	B	C	D
1	New Date	Used Date	Warranty Date	Status
2	2022.06.06	0000.00.00	2022.06.06	Renewed
3	2022.05.01	0000.00.00	2022.05.01	Renewed
4	2021.04.30	2022.04.30	2022.04.30	Renewed
5	2020.04.30	2022.04.30	2022.04.30	Renewed
6	2020.04.30	2022.04.30	2022.04.30	Renewed
7	2020.04.30	2022.04.30	2022.04.30	Renewed
8	2020.04.30	2022.04.30	2022.04.30	Renewed
9	2020.04.30	2022.04.30	2022.04.30	Renewed
10	2020.04.30	2022.04.30	2022.04.30	Renewed
11	2020.04.30	2022.04.30	2022.04.30	Renewed
12	2020.04.30	2022.04.30	2022.04.30	Renewed
13	2018.04.30	2022.04.30	2022.04.30	Renewed
14	2018.04.30	2022.04.30	2022.04.30	Renewed
15	2018.04.30	2022.04.30	2022.04.30	Renewed
16	2018.04.30	2022.04.30	2022.04.30	Renewed
17	2018.04.30	2022.04.30	2022.04.30	Renewed
18	2017.04.30	2022.04.30	2022.04.30	Renewed
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=IF(B2="0000.00.00",A2,B2)
D2:D18	D2	=IF(C2>EOMONTH(TODAY(),3),"Renewed",B2)
C4:C18	C4	=IF(B4="0000.00.00",TEXT(A4,"YYYY.MM.DD"),TEXT(B4,"YYYY.MM.DD"))

 

[bbrnx19]

Revise Column D formula

=IF(C2>TEXT(EOMONTH(TODAY(),3),"yyyy.mm.dd"),"Renewed",B2)

 

[6StringJazzer]

EOMONTH(TODAY(),3) gives you the last day of the month that is 3 months from this month. That is, for today, January 6, it will return April 30, 2022. All of the dates you showed here are April 30 or later, which is why they are all "renewed."

Even if you corrected the formula to be 3 months from today

=IF(C3>DATE(YEAR(TODAY(),MONTH(TODAY())+3,DAY(TODAY()),"Renewed",B3)

you would be checking against April 7, even earlier. And they would still all be "renewed".

So the results you showed above are perfectly consistent with what you said you wanted. What is the problem?

 

[ACQ]

EOMONTH(TODAY(),3) gives you the last day of the month that is 3 months from this month. That is, for today, January 6, it will return April 30, 2022. All of the dates you showed here are April 30 or later, which is why they are all "renewed."

Even if you corrected the formula to be 3 months from today

=IF(C3>DATE(YEAR(TODAY(),MONTH(TODAY())+3,DAY(TODAY()),"Renewed",B3)

you would be checking against April 7, even earlier. And they would still all be "renewed".

So the results you showed above are perfectly consistent with what you said you wanted. What is the problem?

Thank you for replying.

My ideal return result is. All dated within APR 2022 will return the said date. And over APR 2022, will return "Renewed".

And this project, it's a monthly exercise. Hence it will be best if can set > 3months in formula.

 

[BigBeachBananas]

It's because it's not in the same date format. Try:

Book1.xlsx
	A	B	C	D
1	New Date	Used Date	Warranty Date	Status
2	2022.06.06	0000.00.00	2022.06.06	Renewed
3	2022.05.01	0000.00.00	2022.05.01	Renewed
4	2021.04.30	2022.04.30	2022.04.30	2022.04.30
5	2020.04.30	2022.04.30	2022.04.30	2022.04.30
6	2020.04.30	2022.04.30	2022.04.30	2022.04.30
7	2020.04.30	2022.04.30	2022.04.30	2022.04.30
8	2020.04.30	2022.04.30	2022.04.30	2022.04.30
9	2020.04.30	2022.04.30	2022.04.30	2022.04.30
10	2020.04.30	2022.04.30	2022.04.30	2022.04.30
11	2020.04.30	2022.04.30	2022.04.30	2022.04.30
12	2020.04.30	2022.04.30	2022.04.30	2022.04.30
13	2018.04.30	2022.04.30	2022.04.30	2022.04.30
14	2018.04.30	2022.04.30	2022.04.30	2022.04.30
15	2018.04.30	2022.04.30	2022.04.30	2022.04.30
16	2018.04.30	2022.04.30	2022.04.30	2022.04.30
17	2018.04.30	2022.04.30	2022.04.30	2022.04.30
18	2017.04.30	2022.04.30	2022.04.30	2022.04.30
Sheet3

Cell Formulas
Range	Formula
C2:C3	C2	=IF(B2="0000.00.00",A2,B2)
D2:D18	D2	=IF(C2>TEXT(EOMONTH(TODAY(),3),"yyyy.mm.dd"),"Renewed",B2)
C4:C18	C4	=IF(B4="0000.00.00",TEXT(A4,"YYYY.MM.DD"),TEXT(B4,"YYYY.MM.DD"))

 

[ACQ]

It's because it's not in the same date format. Try:

Book1.xlsx
	A	B	C	D
1	New Date	Used Date	Warranty Date	Status
2	2022.06.06	0000.00.00	2022.06.06	Renewed
3	2022.05.01	0000.00.00	2022.05.01	Renewed
4	2021.04.30	2022.04.30	2022.04.30	2022.04.30
5	2020.04.30	2022.04.30	2022.04.30	2022.04.30
6	2020.04.30	2022.04.30	2022.04.30	2022.04.30
7	2020.04.30	2022.04.30	2022.04.30	2022.04.30
8	2020.04.30	2022.04.30	2022.04.30	2022.04.30
9	2020.04.30	2022.04.30	2022.04.30	2022.04.30
10	2020.04.30	2022.04.30	2022.04.30	2022.04.30
11	2020.04.30	2022.04.30	2022.04.30	2022.04.30
12	2020.04.30	2022.04.30	2022.04.30	2022.04.30
13	2018.04.30	2022.04.30	2022.04.30	2022.04.30
14	2018.04.30	2022.04.30	2022.04.30	2022.04.30
15	2018.04.30	2022.04.30	2022.04.30	2022.04.30
16	2018.04.30	2022.04.30	2022.04.30	2022.04.30
17	2018.04.30	2022.04.30	2022.04.30	2022.04.30
18	2017.04.30	2022.04.30	2022.04.30	2022.04.30
Sheet3

Cell Formulas
Range	Formula
C2:C3	C2	=IF(B2="0000.00.00",A2,B2)
D2:D18	D2	=IF(C2>TEXT(EOMONTH(TODAY(),3),"yyyy.mm.dd"),"Renewed",B2)
C4:C18	C4	=IF(B4="0000.00.00",TEXT(A4,"YYYY.MM.DD"),TEXT(B4,"YYYY.MM.DD"))

Stupid me.

Thanks a lot!!!!!!!!!!!!!!!

 

[ACQ]

It's me again. As I proceed with the data, I found more logic test need to add, and I'm lost. Need help again.

1) > 3M return Renewed
2) < 2M return Expired
3) =3M return Date

However, I tried combine formula but said "Too many arguments"

How can I combine formula for d2 and d11 and return the results?

TIA

Book2
	A	B	C	D
1	New Date	Used Date	Warranty Date	Status
2	2022.06.06	0000.00.00	2022.06.06	Renewed
3	2022.05.01	0000.00.00	2022.05.01	Renewed
4	2018.04.30	2022.04.30	2022.04.30	2022.04.30
5	2018.04.30	2022.04.30	2022.04.30	2022.04.30
6	2018.04.30	2022.04.30	2022.04.30	2022.04.30
7	2018.04.30	2022.04.30	2022.04.30	2022.04.30
8	2018.04.30	2022.04.30	2022.04.30	2022.04.30
9	2017.04.30	2022.04.30	2022.04.30	2022.04.30
10	2022.04.01	0000.00.00	2022.04.01	2022.04.01
11	2018.02.23	2022.03.11	2022.03.11	Expired
12	2020.01.02	2022.01.02	2022.01.02	Expired
13	2018.05.29	2020.05.29	2020.05.29	Expired
Sheet1

Cell Formulas
Range	Formula
C2:C13	C2	=IF(B2="0000.00.00",TEXT(A2,"YYYY.MM.DD"),TEXT(B2,"YYYY.MM.DD"))
D2:D10	D2	=IF(C2>TEXT(EOMONTH(TODAY(),3),"yyyy.mm.dd"),"Renewed",C2)
D11:D13	D11	=IF(C11<=TEXT(EOMONTH(TODAY(),2),"yyyy.mm.dd"),"Expired",C11)

 

[bbrnx19]

Have a look at IFS Function which is new in version 2016 or a nested IF
Standard IF you have used

=IF(logical_test,[value_if_true],[value_if_false])

Nested IF

=IF(logical_test 1,[value_if_true 1],IF(logical_test 2,[value_if_true 2],[value_if_false 3]))

 

[BigBeachBananas]

What about between 2M and 3M? Based on the info provided:

Book1.xlsx
	A	B	C	D
1	New Date	Used Date	Warranty Date	Status
2	2022.06.06	0000.00.00	2022.06.06	Renewed
3	2022.05.01	0000.00.00	2022.05.01	Renewed
4	2018.04.30	2022.04.30	2022.04.30	2022.04.30
5	2018.04.30	2022.04.30	2022.04.30	2022.04.30
6	2018.04.30	2022.04.30	2022.04.30	2022.04.30
7	2018.04.30	2022.04.30	2022.04.30	2022.04.30
8	2018.04.30	2022.04.30	2022.04.30	2022.04.30
9	2017.04.30	2022.04.30	2022.04.30	2022.04.30
10	2022.04.01	0000.00.00	2022.04.01	2022.04.01
11	2018.02.23	2022.03.11	2022.03.11	Expired
12	2020.01.02	2022.01.02	2022.01.02	Expired
13	2018.05.29	2020.05.29	2020.05.29	Expired
Sheet5

Cell Formulas
Range	Formula
C2:C13	C2	=IF(B2="0000.00.00",TEXT(A2,"YYYY.MM.DD"),TEXT(B2,"YYYY.MM.DD"))
D2:D13	D2	=IFS(C2<=TEXT(EOMONTH(TODAY(),2),"yyyy.mm.dd"),"Expired",C2>TEXT(EOMONTH(TODAY(),3),"yyyy.mm.dd"),"Renewed",TRUE,C2)