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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓

    @Adarian Searcy

    =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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/19/24

    @Adarian Searcy

    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.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭

    @Adarian Searcy

    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.

  • @Mark.poole

    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.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓

    @Adarian Searcy

    =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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!