If the responding answer of two dates = 0, round to 1

Options

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), ""))

• ✭✭✭✭✭✭
Options

=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.

• ✭✭✭✭✭✭
edited 06/19/24
Options

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.

• Options

@Mark.poole thanks for reaching out. I applied the formula provided and the cell return still reflects 0.

• ✭✭✭✭✭✭
Options

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.

• Options

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.

• ✭✭✭✭✭✭
Options

=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.

• Options

@Mark.poole This formula worked. Thank you for the assistance!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!