Need to create a formula for date management that has different sets of parameters

[HedyES]

I have a date column - cell L5, and in cell M5 I would like to have values 0, 1, 2, 3, or 4 appear based on L5, the current date/today's date, and the following parameters:

0 would appear if the date value in L5 is "COMPLETED," "NEEDED" or "N/A"

1 would appear if the date value in L5 is between 0 to 7 days from the current date (EX today is 1/11/18 and the date in column L5 is 1/4/18, so a value of 1 would appear in cell M5)

2 would appear if the date value in L5 is between 8 to 14 days from the current date (EX today is 1/11/18 and the date in column L5 is 12/26/17, so a value of 2 would appear in cell M5)

3 would appear if the date value in L5 is 15 days or more prior to today's date (EX today is 1/11/18 and the date in column L5 is 12/15/17, so a value of 3 would appear in cell M5)

4 would appear if the date value in L5 is greater than (in the future) today's date (EX today is 1/11/18 and the date in column L5 is 2/1/18, so a value of 4 would appear in cell M5)

I hope that I have explained this well. Thank you

 

[jtakw]

Hi,

How about this:

Book1
	L	M
5	1/4/2018	1
6	12/28/2017	2
7	12/15/2017	3
8	2/1/2018	4
9	COMPLETED	0
Sheet15

Cell Formulas
Range	Formula
M5	=IF(ISTEXT(L5),0,IFERROR(LOOKUP(TODAY()-L5,{0,8,15},{1,2,3}),4))