Calculating number of days from "today" and assigning a value of 0, 1, 2 or 3

I think I may have explained the request incorrectly and want to open this back up with a better worded problem statement:

How do I create a the formula using the following conditions for todays date:

0 would be for blank cells or cells that have the word "COMPLETE" or "N/A" in them
1 would be for cells that are 7 prior to today's date
2 would be for cells that are 8 to 14 days prior to today's date
3 would be for cells that are 15 days prior to today's date
4 would be for dates in the future from today's date

For example if the date 01/03/2018 is in cell A2 and today's date is 01/11, 2018, the number of days is 8 and thus the value assigned via the formula will be 2.

Thank you

Try

=IF(OR(A2="COMPLETE",A2="N/A",LEN(A2)=0),0,LOOKUP(TODAY()-A2,{0,8,15},{1,2,3})

See if this works (untested)

=IF(OR(L5="NEED",L5="COMPLETED",L5="N/A",LEN(L5)=0),0,LOOKUP(L5-TODAY(),{-15,-8,0,1},{3,2,1,4}))

You havent stated what return value you want for dates in the future, I picked 4.
If you want other barriers in the future add them after the 1 in the first set of braces, and remember to add an accompanying return value in the second set of braces

make a blank sheet

in A2 put =TODAY()
in A1 put =A2+1
in A3 put =A2-1
in A4 put =A2-9
in A5 put =A2-16

These values each lie within the four areas that you've defined in your original post: future dates, 0-7 in the past 8-15 in the past, more than 15 days in the past.

in B1
=IF(OR(A1="NEED",A1="COMPLETED",A1="N/A",LEN(A1)=0),0,LOOKUP(A1-TODAY(),{-9.99999999999999E+307,-15,-8,0,1},{3,2,1,0,4}))
and copy down

You should get a column with 4, 0, 1, 2, 3