Forum Discussion
Cell value based on if it contains text, or if it contains a date, how the date relates to today
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 ...
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 )
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
)
Legend! Thank you so much Lorenzo!
You're welcome jesswillerton *
At the bottom of each reply here there's a Mark as response link. Clicking it helps people who Search 😉
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 ) )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) )
