SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2169507%22%20slang%3D%22en-US%22%3ECurrent%20date%20to%20be%20subtracted%20from%20a%20written%20date%2C%20showing%20an%20answer%20based%20on%20the%20%23%20of%20days%20passed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2169507%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20formula%20that%20will%20show%204%20potential%20different%20options%20in%20a%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20Todays%20date%20is%20equal%20or%20less%20than%203%20days%20of%20that%20written%20date%20in%20a%20cell%20than%20the%20answer%20is%20%22less%20than%203%20business%20days%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%204%20answers%20I%20need%20are%3A%3C%2FP%3E%3CP%3ELess%20than%203%20business%20days%3C%2FP%3E%3CP%3ELess%20than%207%20business%20days%3C%2FP%3E%3CP%3ELess%20than%2030%20business%20days%3C%2FP%3E%3CP%3EOver%2030%20business%20days%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20different%20options%20using%20the%20today()%20function%2C%20but%20I%20can't%20seem%20to%20get%20something%20to%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2169507%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2169582%22%20slang%3D%22en-US%22%3ERe%3A%20Current%20date%20to%20be%20subtracted%20from%20a%20written%20date%2C%20showing%20an%20answer%20based%20on%20the%20%23%20of%20days%20pass%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2169582%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F980108%22%20target%3D%22_blank%22%3E%40NoraJJ%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20cell%20is%20A2.%3C%2FP%3E%0A%3CP%3EFormula%20in%20another%20cell%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DLOOKUP(NETWORKDAYS(A2%2CTODAY())%2C%7B-1000%2C4%2C8%2C31%7D%2C%7B%22Less%20than%203%20business%20days%22%2C%22Less%20than%207%20business%20days%22%2C%22Less%20than%2030%20business%20days%22%2C%22Over%2030%20business%20days%22%7D)%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.