30-60 -90 Days - IF Function

[Jatli]

Hi

I am trying to do a function where if invoice is 30-60 days the cell will say30 - 60 days, if invoice is 60-90 days, cell will say 60 to 90 days and if invoice is over 90 days, cell will say Over 90 Days. Here is the formula I have put it =IF(H3>29,"30-60 Days",IF(H3>59,"60-90 Days",IF(H3>90,"Over 90 Days","")))

It is not working as even when it is over 60 days it will still say 30-60 days. If I put <60 in the first one then cells that are only 1 day old will say 30-60 days. Please help.

 

[Weazel]

try it this way...

=IF(H3>90,"Over 90 Days",IF(H3>59,"60-90 Days",IF(H3>29,"30-60 Days","")))

 

[admiral100]

Anther way :

=LOOKUP(H3,{1,61,91},{"30-60 Days","60-90 Days","Over 90 Days"})

 

[Weazel]

mmmm, that's still going to give you 30-60 days if H3 is less than 30...you could rewrite is like

=LOOKUP(H3,{0,30,60,90},{"","30-60 Days","60-90 Days","Over 90 Days"})

maybe

 

[Jatli]

That worked perfectly. Thank you so much ??

 

[lzweifel]

Hello.... I am trying to create a formula to do exactly this, except rather than the cell read "30-60 Days, 60-90 Days, etc I want it to put the Invoice amount in that column.

 

[BSALV]

something like this ?(but a Vlookup is easier to maintain

=LOOKUP(H3,{0,10,100,1000},{"small invoice","medium invoice","big invoice","very big invoice"})

 

[lzweifel]

something like this ?(but a Vlookup is easier to maintain

=LOOKUP(H3,{0,10,100,1000},{"small invoice","medium invoice","big invoice","very big invoice"})

Oh shoot... NO, I am not trying to look something up. I want the invoice amount to appear in the appropriate column based on the date. So an invoice from Dec 1 would now appear in the OVER 30 day column and on Feb 1 would then jump to display in the 60 day column. Sorry for the confusion.

 

[Toadstool]

Late to the party but I like the MATCH & CHOOSE approach in column G

Jatli.xlsx
	A	B	C	D	E	F	G
1	Date		MATCH		Days		Result
2	1/1/2021		5		370		Over 90 Days
3	10/7/2021		5		91		Over 90 Days
4	10/8/2021		4		90		61-90 Days
5	10/9/2021		4		89		61-90 Days
6	11/1/2021		4		66		61-90 Days
7	11/6/2021		4		61		61-90 Days
8	11/7/2021		3		60		31-60 Days
9	11/8/2021		3		59		31-60 Days
10	12/1/2021		3		36		31-60 Days
11	12/6/2021		3		31		31-60 Days
12	12/7/2021		2		30		0-30 Days
13	12/8/2021		2		29		0-30 Days
14	1/1/2022		2		5		0-30 Days
15	1/6/2022		2		0		0-30 Days
16	4/4/2022		1		-88		Future Date
Sheet1

Cell Formulas
Range	Formula
C2:C16	C2	=MATCH(TODAY()-A2,{-99999,0,31,61,91,99999},1)
E2:E16	E2	=TODAY()-A2
G2:G16	G2	=CHOOSE(MATCH(TODAY()-A2,{-99999,0,31,61,91,99999},1),"Future Date","0-30 Days","31-60 Days","61-90 Days","Over 90 Days")

 

[Toadstool]

Oh shoot... NO, I am not trying to look something up. I want the invoice amount to appear in the appropriate column based on the date. So an invoice from Dec 1 would now appear in the OVER 30 day column and on Feb 1 would then jump to display in the 60 day column. Sorry for the confusion.

Hi Izweifel,

Just match the result to your heading, e.g.

Izweifel.xlsx
	A	B	C	D	E	F	G	H
1	Invoice Amount	Date			0-30 Days	31-60 Days	61-90 Days	Over 90 Days
2	$ 74.00	1/1/2021						$ 74.00
3	$ 111.00	10/7/2021						$ 111.00
4	$ 148.00	10/8/2021					$ 148.00
5	$ 185.00	10/9/2021					$ 185.00
6	$ 222.00	11/1/2021					$ 222.00
7	$ 259.00	11/6/2021					$ 259.00
8	$ 296.00	11/7/2021				$ 296.00
9	$ 333.00	11/8/2021				$ 333.00
10	$ 370.00	12/1/2021				$ 370.00
11	$ 407.00	12/6/2021				$ 407.00
12	$ 444.00	12/7/2021			$ 444.00
13	$ 481.00	12/8/2021			$ 481.00
14	$ 518.00	1/1/2022			$ 518.00
15	$ 555.00	1/6/2022			$ 555.00
16	$ 592.00	4/4/2022
Sheet1

Cell Formulas
Range	Formula
E2:H16	E2	=IF(CHOOSE(MATCH(TODAY()-$B2,{-99999,0,31,61,91,99999},1),"Future Date","0-30 Days","31-60 Days","61-90 Days","Over 90 Days")=E$1,$A2,"")