If & Date Calculations

Sue Pemberton

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

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)

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! 👍️