Excel formula for this scenario

[Stylikzer]

Hi. I am not good with formulas and need help writing this one. It pertains to date calculations if less than 30 days, 30-60 days, or greater than 60 days

What I am looking for is if Field O2 is >0 show"Ignore", if O2 is <0, then proceed to subtract Today's date from the start date field. If date calculation is <30 days, show < 30 days. If date calculation is between 30-60 days, show 30-60 days. If the date calculation >60 days, show >60 days.

Any help with this will be appreciated.

 

[CA_Punit]

You have not clarified what if O2=0, Considering O2=0

Book1
	O	P	Q
1		Start Date
2	-1	09/07/2020	<30
3
Sheet1

Cell Formulas
Range	Formula
Q2	Q2	=IF(O2>=0,"Ignore",LOOKUP(TODAY()-P2,{0;31;61},{"<30";"30-60";">60"}))

 

[Toadstool]

Hi Stylikzer,

Is this what you wanted?

Stylikzer.xlsx
	H	I					N
1	Start Date	Response
2	01-Jan-20	>60					0
3	07-Jun-20	>60
4	08-Jun-20	>60
5	06-Jul-20	30-60
6	08-Jul-20	30-60
7	09-Jul-20	<30
8	30-Jul-20	<30
9	11-Aug-20	<30
10	23-Aug-20	<30
11	04-Sep-20	<30
Sheet1

Cell Formulas
Range	Formula
I2:I11	I2	=IF($N$2>0,"Ignore",CHOOSE((TODAY()-H2>59)+(TODAY()-H2>29)+1,"<30","30-60",">60"))

 

[Stylikzer]

Hi CA_Punit and Toadstool
Thank you for your help. I tried both of your formulas, but when i executed it, I got "ignore" for all results when it should have populated the <30 or 30-60 or >60
=IF(O380>=0,"Ignore",LOOKUP(TODAY()-H380,{0;31;61},{"<30";"30-60";">60"}))
=IF(O373>0,"Ignore",CHOOSE((TODAY()-H373>59)+(TODAY()-H373>29)+1,"<30","30-60",">60"))

I am not sure what I am doing wrong?

 

[CA_Punit]

What you have in O380???

 

[Stylikzer]

It is a formula
=if(A374="","",IFERROR(if(networkdays(H374,N374)>=0,networkdays(H374,N374),0),"-"))

 

[Toadstool]

So "-" is a character string so resolves as greater than zero.
You can change your IFERROR to return 0 instead of "-" or my =IF(O373>0,"Ignore" to =IF(O373="-","Ignore"