Help with formula for Current, Next, Last week calc based on date

Craig H

I admit.. I'm gonna be lazy on this one. lol. I'm brand new to smart sheet.

We have a field called [Due Date] that is a date type field. I want to create a string field that follows this logic:

IF [Due Date] occurs during this week THEN "This Week"

ELSEIF [Due Date] occurs next week THEN "Next Week"

ELSEIF [Due Date] occurs last week THEN "Last Week"

ELSEIF [Due Date] occurs after next week THEN "After Next Week "

ELSEIF [Due Date] occurs before last week then "Before Last Week"

ELSE null()

I'm used to using Alteryx where I can write this exactly like I wrote it, but I'm having trouble achieving this using the nested if syntax of Smartsheet.

Thanks for any help!,

Craig

Find more posts tagged with

Formulas

Accepted answers

Craig H

I figured it out

=IF( Status@row = "Complete", "Complete",

IF( ISBLANK([Due Date]@row), "No Due Date",

IF( WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()), "This Week",

IF( WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) + 1, "Next Week",

IF( WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) - 1, "Last Week",

IF( WEEKNUMBER([Due Date]@row) > WEEKNUMBER(TODAY()) + 1, "After Next Week",

IF( WEEKNUMBER([Due Date]@row) < WEEKNUMBER(TODAY()) - 1, "Before Last Week")))))))

All comments

KPH

The syntax for the nested IF would be

=IF( [Due Date] occurs during this week , "This Week",

IF( [Due Date] occurs next week , "Next Week",

IF( [Due Date] occurs last week , "Last Week",

IF( [Due Date] occurs after next week , "After Next Week ",

IF( [Due Date] occurs before last week , "Before Last Week")))))

Each elseif is a new IF(

There is a comma to separate the logic and the result if true

Then another comma to separate the result if true and the result if false (which in your case is another IF)

You need to replace the part in bold with your logic to determine what constitutes "this week", etc.

Craig H

@KPH

The "occurs during" logic is really the part I needed help with. I would have eventually figured out the nested if part as I have done nested IFs in excel

Craig H

I figured it out

=IF( Status@row = "Complete", "Complete",

IF( ISBLANK([Due Date]@row), "No Due Date",

IF( WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()), "This Week",

IF( WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) + 1, "Next Week",

IF( WEEKNUMBER([Due Date]@row) = WEEKNUMBER(TODAY()) - 1, "Last Week",

IF( WEEKNUMBER([Due Date]@row) > WEEKNUMBER(TODAY()) + 1, "After Next Week",

IF( WEEKNUMBER([Due Date]@row) < WEEKNUMBER(TODAY()) - 1, "Before Last Week")))))))

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.