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

Find more posts tagged with

Accepted answers

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.

All comments

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

This should cover everything.

Adarian Searcy

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.