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

Adarian Searcy

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

Mark.poole

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

Mark.poole

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

Adarian Searcy

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

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

Adarian Searcy

@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

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

Adarian Searcy

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