Date or N/A in a cell
I am trying to figure out a column formula that will auto-populate a cell with either a date or an N/A:
N/A, if the [TOTAL BUDGET] cell is under $100,000
or
=[Final Report Due]@row + 15 if the [TOTAL BUDGET] cell@row is $100,000 or more (this would be the audit report due date)
I tried this but it's not quite working:
=IF([TOTAL BUDGET]@row < $100,000, "N/A", IF([TOTAL BUDGET]@row >= $100,000, "[Final Report Due]@row +15"))
One problem seems to be that if I set the Column Type to "Date", then putting N/A isn't possible. But if I set the Column Type to Text/Number, the date calculations don't work.
Is there another way to approach this?
Thank you in advance for your help!
Best Answers
-
Hi @LizTo,
=IF([TOTAL BUDGET]@row < 100000, [Final Report Due]@row + 15, "N/A")
Ensure the column you're calculating in is a Date column, but not restricted to Date values which allows for the "N/A".
All the best,
-Ray
-
Awesome, glad you got it!
You could have also just flipped the < to a and left the results in that order. Either way would accomplish the same.
=IF([TOTAL BUDGET]@row < 100000, [Final Report Due]@row + 15, "N/A")
would become
=IF([TOTAL BUDGET]@row > 100000, [Final Report Due]@row + 15, "N/A")
Answers
-
Hi @LizTo,
=IF([TOTAL BUDGET]@row < 100000, [Final Report Due]@row + 15, "N/A")
Ensure the column you're calculating in is a Date column, but not restricted to Date values which allows for the "N/A".
All the best,
-Ray
-
-
Awesome, glad you got it!
You could have also just flipped the < to a and left the results in that order. Either way would accomplish the same.
=IF([TOTAL BUDGET]@row < 100000, [Final Report Due]@row + 15, "N/A")
would become
=IF([TOTAL BUDGET]@row > 100000, [Final Report Due]@row + 15, "N/A")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!