IF Formula

Hello,

I am struggling for while creating one formula.

In my Smartsheet I have Column "Status" with different statuses from a dropdown. One of them is Completed. I also have a Column "End Date" where different dates are being selected.

I want to create a Column "Last 14 days" and one "Next 14 Days" for a report. I need a formula that automatically fills in this columns with yes or no.

In "Last 14 days": Display Yes, if Status =Completed and End Date lies in the last 14 days.

In "Next 14 days": Display Yes, if Status =Completed and End Date lies in the next 14 days.

I am trying this but it does not work for me.

=IF(AND(Status = "Completed", End Date =< TODAY (-14)()), [Last 14 days], yes, no)

=IF(AND(Status = "Completed", End Date =< TODAY (+14)()), [Last 14 days], yes, no)

I hope someone can help.

Thanks

Veli

Find more posts tagged with

Accepted answers

Paul Newcome

You do not need to replace @row with anything. You can leave it as is. Try changing the commas to semi-colons.

All comments

Paul Newcome

Try these...

=IF(AND(Status = "Completed", End Date => TODAY (-14)), [Last 14 days], yes, no)

=IF(AND(Status = "Completed", End Date =< TODAY (14)), [Last 14 days], yes, no)

Velislava Zagorcheva

Still no success. When I copy paste in the rows of the Last 14 days Column , it states #Unparseable. Anyway thanks for the quick answer.

Paul Newcome

My apologies. I misread your original formulas.

Last 14 Days:

=IF(AND(Status = "Completed", [End Date]@row >= TODAY (-14), [End Date]@row <= TODAY()), "yes", "no")

Next 14 Days:

=IF(AND(Status = "Completed", [End Date]@row <= TODAY (14), [End Date]@row >= TODAY()), "yes", "no")

Velislava Zagorcheva

Hi Paul,

Where it says @row do I replace that with just the number of that row? Like this:

=IF(AND(Status = "Completed", [End Date]1 >= TODAY (-14), [End Date]1 <= TODAY()), "yes", "no")

If yes this still does not work for me. I paste the above in a cell at the end on the row 1 & it displays again #Unparseable, instead yes or no.

Regards

Veli

Paul Newcome

You do not need to replace @row with anything. You can leave it as is. Try changing the commas to semi-colons.

Velislava Zagorcheva

Thanks a lot Paul for the help. Now it works. I also exchanged Status = with Status@row

Paul Newcome

Sorry for missing that @row on Status. Glad you were able to get it working. 👍️

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.