SOLVED

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

Occasional Contributor

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.

4 Replies
best response confirmed by NoraJJ (Occasional Contributor)
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"})

Hi @Hans Vogelaar 

 

Thank you for talking the time to respond!

 

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

 

NJ

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

@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.