Formula to display text based on date range of another cell

[iluvweed]

Hello I am trying to use one cell to display 4 different lines of text based on the date of a single cell. Specifically what I am trying to do is have a cell display "30 days" if the date in another cell (B4) is within 30 days of TODAY. Once the date is within 60 days of TODAY I want it to read "60 days". Once the date is within 90 days of TODAY, I want it to read "90 days". Once 120 days have passed I don't know what I want it to read yet. Probably blank for now (""). I'm getting close on my own but not close enough. I cant add all 4 of these functions into a formula with my limited knowledge. Any insight would be very much appreciated! Thank you!!!

 

[etaf]

is the date in the cell in the future or past

so if today is 28-nov
then 30 days in future - is that having the date 28/12/22 in the cell
OR
30 days in past is that having the date 29/10/22 in the cell

something like a nested if would do that

=IF( Cell -today() ) < 30 ,
OR
If(today() - cell ) <30 ,

But i'm not sure what within exactly means

 

[iluvweed]

is the date in the cell in the future or past

so if today is 28-nov
then 30 days in future - is that having the date 28/12/22 in the cell
OR
30 days in past is that having the date 29/10/22 in the cell

something like a nested if would do that

=IF( Cell -today() ) < 30 ,
OR
If(today() - cell ) <30 ,

But i'm not sure what within exactly means

thank you for replying!
The date in B4 is a hire date for an employee. So would pretty much always be in the past. I want a cell to reflect if they are within their 30 days of hire (and then say 30), and then change when they are within 60 days and ultimately 90 days. Once the 90 are up the cell would change to at this point nothing because I want to utilize the cell to display a text I don't have a use for yet. I figured I just leave it blank for the time being. I will work now on what you gave me and hopefully that works for me.

 

[iluvweed]

This is what I have been trying to make work for me, I think i'm getting close...

=IF(B4<TODAY()+30,"30",IF(B4<TODAY()+60,"60",IF(B4<TODAY()+90,”90”)))

 

[etaf]

=IF((TODAY()-B4)>120, "over120",IF((TODAY()-B4)>90, "over90", IF((TODAY()-B4)>60, "over60",IF((TODAY()-B4)>30, "over30","under 30"))))

Book3
	A	B	C	D
1
2
3
4		5/13/22		over120
5		6/2/22		over120
6		6/22/22		over120
7		7/12/22		over120
8		8/1/22		over120
9		8/21/22		over90
10		9/10/22		over60
11		9/30/22		over60
12		10/20/22		over30
13		11/9/22		under 30
14		11/29/22		under 30
Sheet1

Cell Formulas
Range	Formula
D4:D14	D4	=IF((TODAY()-B4)>=120, "over120",IF((TODAY()-B4)>=90, "over90", IF((TODAY()-B4)>=60, "over60",IF((TODAY()-B4)>=30, "over30","under 30"))))

Book3
	A	B	C	D	E	F
1
2
3						Example of days calc
4		5/13/22		over120		200
5		6/2/22		over120		180
6		6/22/22		over120		160
7		7/12/22		over120		140
8		8/1/22		over120		120
9		8/21/22		over90		100
10		9/10/22		over60		80
11		9/30/22		over60		60
12		10/20/22		over30		40
13		11/9/22		under 30		20
14		11/29/22		under 30		0
Sheet1

Cell Formulas
Range	Formula
D4:D14	D4	=IF((TODAY()-B4)>=120, "over120",IF((TODAY()-B4)>=90, "over90", IF((TODAY()-B4)>=60, "over60",IF((TODAY()-B4)>=30, "over30","under 30"))))
F4:F14	F4	=TODAY()-B4

 

[iluvweed]

This totally works thank you sooo much!! I cant thank you enough truly, you saved me soo much time.

 

[etaf]

you are welcome
I was getting confused when i posted - if you wanted within 30,60,90,120

Anyway - I'll leave it in case

OR
if its within those days
how about

Book3
	A	B	C	D	E	F
1
2
3						Example of days calc
4		5/13/22		120 days or older		200
5		6/2/22		120 days or older		180
6		6/22/22		120 days or older		160
7		7/12/22		120 days or older		140
8		8/1/22		120 days or older		120
9		8/21/22		120 days		100
10		9/10/22		90 days		80
11		9/30/22		90 days		60
12		10/20/22		60 days		40
13		11/9/22		30 days		20
14		11/29/22		30 days		0
Sheet1

Cell Formulas
Range	Formula
D4:D14	D4	=IF((TODAY()-B4)<30, "30 days",IF((TODAY()-B4)<60, "60 days", IF((TODAY()-B4)<90, "90 days",IF((TODAY()-B4)<120, "120 days","120 days or older"))))
F4:F14	F4	=TODAY()-B4

 

[iluvweed]

Ya I went through and just changed the text to "within" and it pretty much works exactly how I needed it. I'm sure I can fiddle with it more but on a time crunch and many other issues to figure out lol. But once again I really appreciate this very much. I looked for a link to send you a beer or a tea or something to show my gratitude but cant seem to find one on your profile, I don't come here very often so unsure if something like that exists. If it does though I'd appreciate the link. True life saver!

 

[etaf]

and just changed the text to "within" and it pretty much works exactly

so long as the dates you want - do work correctly for you
BUT
Within sounds to me more like post 7
=IF((TODAY()-B4)<30, "within 30 days",IF((TODAY()-B4)<60, "Within 60 days", IF((TODAY()-B4)<90, "Within 90 days",IF((TODAY()-B4)<120, "Within 120 days","120 days or older"))))

Anyway so long as it does what you need with various dates tests ......

I looked for a link to send you a beer or a tea or something to show my gratitude

Thats very kind of you , no link for payment to a member or to the site as far as I know, This is a free volunteer site here , so thanks for the offer very kind.