IF Statement Greater than, Less Than + Math = text

[maurialero]

Hello Everyone,

I can't wrap my head around this one and I thought it was going to be simple....

Here is the issue:

Column A has an expiration date
Column B has the Todays formula. Why? I want the formula to tell me when an expiration is becoming due, is current or expired.

You can see I'm adding day formulas also. A little guidance will be appreciated.

I'm just comparing a column to a date not comparing two columns. Then I expect 3 different answers based on my conditions but it doesnt seems to work.

Example: =IF(A1-110<B1, "Take Action", IF(A1=B1, "Expired", IF(A1-365=B1, "Current"

 

[HongRu]

How about this?

活頁簿3
	A	B	C	D
1	Expiration	Today	Remark	Note
2	2022/12/13	2022/12/13	Expired	equal to today
3	2022/12/12	2022/12/13	Expired	later than today
4	2022/12/14	2022/12/13	Take Action	earlier than today under 110 days
5	2023/4/1	2022/12/13	Take Action	earlier than today under 110 days
6	2023/4/2	2022/12/13	Take Action	earlier than today under 110 days
7	2023/4/3	2022/12/13	Current	earlier than today over 110 days
工作表1

Cell Formulas
Range	Formula
A2	A2	=B2
B2:B7	B2	=TODAY()
C2:C7	C2	=IF(A2<=B2,"Expired",IF(A2>B2+110,"Current","Take Action"))
A3	A3	=B3-1
A4	A4	=B4+1
A5	A5	=B5+109
A6	A6	=B6+110
A7	A7	=B7+111

 

[maurialero]

This is awesome! Maybe I'm not as good with Excel as I thought but let me ask something else please. It seems that it works but the problem is that the math in embeded basically what I'm looking for is a shell formula.

Example:

I open my spreadsheet and in one cell out of the view the formula =Today() automatically refreshes.

Now the new report I will paste has:
Work order numbers
Fr1 - Exp date
Fr2 - Exp date
Fr3 - Exp date
Etc.. in a sequencial waterfall order.

Now column 3 has my formula I just copy paste and drag down. In other words the calculations have to happen withinn the formula as opposed to be added to the dates.

 

[HongRu]

So what is your need ?
I can't understand your needs clearly.

 

[maurialero]

So what is your need ?
I can't understand your needs clearly.

Thank you . I will post the spreadsheet in a bit and maybe that helps.

Thank you for reminding me about the clear and concise part.

 

[maurialero]

Cant seem to be able but here is the formula:

=IF($E10-110<=$N$1,"Expired","Current")

Column A =
7/1/2023

Column B = Result from formula

Column C (In only one cell) =Today()

 

[Alex Blakenburg]

I am wondering if you are misreading HongRu's XL2BB.
The formula in Column A are purely to produce representative sample dates, they are not part of the calculation.
The XL2BB has the formula in Column C with column B just repeating Today(), so you can put the formula in Column B and change any references to column B with the Cell you have containg Today().
The Check Columns is just informational to help check the logic.

Book1
	A	B	C	D	E	F	G
1	Expiration	Remark	Note	Check Only Days since today		Date To Use	13/12/2022
2	13/12/2022	Expired		0
3	12/12/2022	Expired		-1
4	14/12/2022	Take Action		1
5	1/04/2023	Take Action		109
6	2/04/2023	Take Action		110
7	3/04/2023	Current		111
Modified

Cell Formulas
Range	Formula
B2:B7	B2	=IF(A2>($G$1+110),"Current",IF(A2>$G$1,"Take Action","Expired"))
D2:D7	D2	=A2-$G$1

 

[maurialero]

Omg you might be right!. First time is use the forum and not familiar with all the features wanted to upload my spreadsheet but didnt work.

This is a great Foroum. I just wish and maybe you can elaborate on this one. Why is it that I didnt do properly? It seems I was missing a lot of elements in my formula.

 

[Alex Blakenburg]

I am not if this will help but lets give it a go.
When you are trying to group numbers (which includes dates) into number ranges, it works best if you do your if statements in order, descending or ascending order.
That way the next if statement is effectively saying BETWEEN.,

20221213 If Date grouping maurialero.xlsx
	A	B
1	Numbers	Grouping
2	10	Under 21
3	20	Under 21
4	30	21-50
5	40	21-50
6	50	21-50
7	100	51-200
8	200	51-200
9	300	>200
Sample

Cell Formulas
Range	Formula
B2:B9	B2	=IF(A2>200,">200", IF(A2>50,"51-200", IF(A2>20,"21-50", "Under 21")))

 

[maurialero]

Yes it does help!. Also I noticed the heriarchy or closing the math between parenthesis.

Another thing it seems that my math was wrong I was trying to do the math within the exp date like this:
A1
7/23/2024

B1
=IF(A1-100<=C1, "Take Action", "Expired")

C1
=Today()

My formula was to simple. I live Excel hopefully I can find good tutoring here or books that help you with nested formulas. I'm interested in the why? As much as the how?

Great help here.