Current date to be subtracted from a written date, showing an answer based on the # of days passed

[NoraJJ]

I need a formula that will show 4 potential different options in a cell.

If Todays date is equal or less than 3 days of that written date in a cell than the answer is "less than 3 business days".

the 4 answers I need are:

Less than 3 business days

Less than 7 business days

Less than 30 business days

Over 30 business days

I've tried different options using the today() function, but I can't seem to get something to work.

[#2 · Accepted solution]

@NoraJJ 

Let's say the cell is A2.

Formula in another cell:

=LOOKUP(NETWORKDAYS(A2,TODAY()),{-1000,4,8,31},{"Less than 3 business days","Less than 7 business days","Less than 30 business days","Over 30 business days"})

[#3]

Hi @Hans Vogelaar 

Thank you for talking the time to respond!

What does {-1000,4,8,31} mean/refer to?

NJ

[#4]

Thank you! The formula worked, my workplace is happy with the result. You have positively impacted my day.

[#5]

@NoraJJ 

{-1000,4,8,31} is an array that contains the thresholds for each of the return values.

The -1000 is an arbitrary very negative number. Anything larger than that but less than the next threshold 4 will return "Less than 3 business days".

4 is the first value for which we return "Less than 7 business days".

Etc.