IF Formula Not Working As Intended

Hi everyone!

The below formula is not populating a 0 in cells that are new, as intended. The AI tool even says this formula should work, but it's not. After staring at it for hours, I can't figure out where I'm going wrong. Your help would be greatly appreciated!! Thanks! :)

=IFERROR([Date Logged]@row - MAX(COLLECT([Date Logged]:[Date Logged], [Issue*]:[Issue*], [Issue*]@row , [Date Logged]:[Date Logged], <[Date Logged]@row )), 0)

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

The problem is with your MAX/COLLECT. There is nothing being brought in with the COLLECT, so the MAX function is outputting zero. A date minus zero outputs that date instead of a number, so your formula is trying to output a date which throws that error when placed in a text/number column. The reason the IFERROR is not working is because technically your base formula (date minus max/collect) is not throwing an error. IFERROR is designed to catch errors only within that first portion of the function, but the error you are seeing is coming from the column type instead.

Here is my suggestion for a formula:

IF(COUNTIFS([Date Logged]:[Date Logged], @cell = [Date Logged]@row, [Issue*]:[Issue*], [Issue*]@row) = 1, 0, [Date Logged]@row - MAX(COLLECT([Date Logged]:[Date Logged], [Issue*]:[Issue*], [Issue*]@row, [Date Logged]:[Date Logged], <[Date Logged]@row))

All comments

Paul Newcome

Here is my suggestion for a formula:

Angelina

Thank you so much!! This fixed the issue!

Paul Newcome

Happy to help. 👍️

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.