Cell value based on if it contains text, or if it contains a date, how the date relates to today

[jesswillerton]

Hi all

I'm having problems writing a formula for this spreadsheet. The purpose is to show when a project needs to be sent. Each project has a deadline (column H), but some projects have a date they can't be sent before (column I). I have created column J which is Actual Go Date, i.e. when it needs to be sent by, or if I can't send it until later then today it says 'wait'.

The problem I'm having is with column K. It is coding the priority based on how the date in column J compares to today's date in C2 (see formula for cell K20), but how do I get it to also say 'wait' if column J has 'wait' in it rather than a date?

Thank you!

[#2 · Accepted solution]

Hi @jesswillerton 

In K14:

=IFS(
  ISTEXT(J14),      J14,
  J14 < C$2,        H$2,
  J14 = C$2,        H$3,
  (J14 - C$2) < 4,  H$4,
  (J14 - C$2) < 8,  H$5,
  TRUE,             H$6
)

[#3]

Legend! Thank you so much @Lorenzo!

[#4]

You're welcome @jesswillerton *

At the bottom of each reply here there's a Mark as response link. Clicking it helps people who Search 😉

[#5]

@jesswillerton 

An alterative (better than IFS) if you run Excel 2021 or 365:

=IF(  ISTEXT(J14),
      J14,
      XLOOKUP( J14 - C$2, {-1;0;4;8;10000}, H$2:H$6,, 1 )
)

[#6]

@Lorenzo 

If change a bit it works practically on any version

=IF(  ISTEXT(J14),
      J14,
      LOOKUP( J14 - C$2, {-10000,0,0.1,4,8,10000}, H$2:H$6)
)