If statement when two conditions are met, return blank cell, else value @row
Hi everyone,
I'm trying to return a blank expiration date (in the Soonest Expiration Date column) if someone has banked holiday hours then later used the hours in full, otherwise keep the expiration date in the cell. This is what I have so far:
=IF(AND({DECW Alternate Holiday Range 1}, "New Year's Day", {DECW Alternate Holiday Range 2}, "New Year's Day"), " ", [Soonest Expiration Date]@row) + IF(AND({DECW Alternate Holiday Range 1}, "Martin Luther King Jr. Day", {DECW Alternate Holiday Range 2}, "Martin Luther King Jr. Day"), " ", [Soonest Expiration Date]@row)
Source Sheet:
Best Answer
-
Hi.. It sounds like you are trying to write a formula for a cell that already has a value and then replace the value if certain conditions are met and leave it alone if those conditions are not met. This cannot be done.
You would need to create a second column that will equal the Soonest Expiration Date column unless those conditions are met. Think of it this way... the first column is where the "default" value is. Then the second column shows the "actual" value based on a bunch of conditions.
I'm not sure this is what you are looking for, so I hope it helps.
Be well
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
Answers
-
Hi.. It sounds like you are trying to write a formula for a cell that already has a value and then replace the value if certain conditions are met and leave it alone if those conditions are not met. This cannot be done.
You would need to create a second column that will equal the Soonest Expiration Date column unless those conditions are met. Think of it this way... the first column is where the "default" value is. Then the second column shows the "actual" value based on a bunch of conditions.
I'm not sure this is what you are looking for, so I hope it helps.
Be well
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
-
Thank you! I appreciate the shifting in thinking on this!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!