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)


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?



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


{-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".