If the responding answer of two dates = 0, round to 1
I am currently creating a formula that has one date subtracted by another date. If the responding numerical digit is 0, then I would like to round to 1. When I input the formula I receive "In Progress" , not the number 1. Any insight?
=IF(NOT(ISBLANK([Primary Review Recommendation Completion Date]@row - [Form Completion Date]@row)), "In Progress", IF(SUM([Primary Review Recommendation Completion Date]@row, [Form Completion Date]@row) = 0, ROUNDUP(0, 1), ""))
Best Answer
-
=IF(ISBLANK([Primary Review Recommendation Completion Date]@row), "In Progress", IF([Primary Review Recommendation Completion Date]@row - [Form Completion Date]@row = 0, 1, [Primary Review Recommendation Completion Date]@row - [Form Completion Date]@row))
This should cover everything.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Answers
-
This would be due to the order of your if statements. Switch them around and that should fix it.
=IF([Primary Review Recommendation Completion Date]@row - [Form Completion Date]@row = 0, ROUNDUP(0, 1), IF(NOT(ISBLANK([Primary Review Recommendation Completion Date]@row - [Form Completion Date]@row)), "In Progress", ""))
It was finding your if statement as true thus it stopped running the false statement.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
@Mark.poole thanks for reaching out. I applied the formula provided and the cell return still reflects 0.
-
well the round up function is trying to round 0 up to the nearest decimal. which is still… 0. instead of using round up try if = 0,1
=IF([Primary Review Recommendation Completion Date]@row - [Form Completion Date]@row = 0, 1, IF(NOT(ISBLANK([Primary Review Recommendation Completion Date]@row - [Form Completion Date]@row)), "In Progress", ""))
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
The formula last supplied did convert the 0 to 1. However when I converted to a column formula, all of my previous numbers changed to "In Progress" and the In Progress converted to "1". I am using the 6.13.24 Form Completion Date as a reference.
-
=IF(ISBLANK([Primary Review Recommendation Completion Date]@row), "In Progress", IF([Primary Review Recommendation Completion Date]@row - [Form Completion Date]@row = 0, 1, [Primary Review Recommendation Completion Date]@row - [Form Completion Date]@row))
This should cover everything.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
@Mark.poole This formula worked. Thank you for the assistance!
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!