If & Date Calculations

Morning,

I am new to smartsheets & having some trouble merging formulas together. I can get them all to work on their own but when I try to add them together they fail. I am obviously missing something.

I have 3 fields;

Expected Completion Date - dd/mm/yyy

Status - Single Selection (Closed, Archived, Open, Overdue)

Due / Overdue - Formula Field

The statements that I have working on there own standing are;

=IF(Status3 = "Archived", "N/A")

=IF(Status3 = "Closed", "N/A")

=SUM([Expected Completion Date]2 - TODAY()) / 31

What I am looking for is something that will populate N/A if the status is Closed or Archived & if it is open or overdue then it will a positive or negative # of months.

Any help is greatly appreciated

thanks

Sue

Find more posts tagged with

Accepted answers

Paul Newcome

That was my fault. Pesky parenthesis. Here. Try this one...

=IF(OR(Status@row = "Archived", Status@row = "Closed"), "N/A", SUM([Expected Completion Date]2 - TODAY()) / 31)

All comments

Paul Newcome

Try something like this...

=IF(OR(Status@row = "Archived", Status@row = "Closed", "N/A", SUM([Expected Completion Date]2 - TODAY()) / 31)

Sue Pemberton

Ok, I can see how this might work however it is providing me now with a different error, "#INCORRECT ARGUMENT SET"

Is this purely saying that the sum should be at the start of the formula?

Thanks for the guidance

Paul Newcome

That was my fault. Pesky parenthesis. Here. Try this one...

=IF(OR(Status@row = "Archived", Status@row = "Closed"), "N/A", SUM([Expected Completion Date]2 - TODAY()) / 31)

Sue Pemberton

A thousand thank you's this is perfect. Have a great day.

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.