Week Number Year to Year

I've got the following formula in a Check Box column to check when something is due in the Next 3 weeks.

=IFERROR(IF(AND(WEEKNUMBER([Projected Cleaning Date]@row ) = WEEKNUMBER(TODAY()) + 3, YEAR([Projected Cleaning Date]@row ) = YEAR(TODAY())), 1), "")

I have them for 2 weeks, 3 weeks, 4 weeks, and 5 weeks. These stopped checking themselves as we approach the New Year. Any suggestions to make these work with a year over year change.

Find more posts tagged with

Formulas

Accepted answers

All comments

amarkumar

https://community.smartsheet.com/discussion/144443/week-number-year-to-year

Hello @jedelman Next 3 weeks:- =IF(AND([Projected Cleaning Date]@row - TODAY() >= 14, [Projected Cleaning Date]@row - TODAY() < 21), 1, "")
Next 2 weeks:- =IF(AND([Projected Cleaning Date]@row - TODAY() >= 7, [Projected Cleaning Date]@row - TODAY() < 14), 1, "")
Next 4 weeks:- =IF(AND([Projected Cleaning Date]@row - TODAY() >= 21, [Projected Cleaning Date]@row - TODAY() < 28), 1, "")

for any other week just change the number.

Paul Newcome

I would suggest something along the lines of…

=IFERROR(IF(AND(WEEKNUMBER([Projected Cleaning Date]@row) = WEEKNUMBER(TODAY(21)), YEAR([Projected Cleaning Date]@row) = YEAR(TODAY(21))), 1), "")

You would only need to adjust the 21 to change the formula for a different number of weeks out. The 21 inside of the TODAY simply adds 21 days to today's date. Then we pull the week number and year from that date.

jedelman

Hi @Paul Newcome ,

Appreciate the input. I tried your equation and it seemed to be working except for In the Next 2 Weeks. I've attached a screenshot. Specifically the checkbox won't check on the 28th, 29th, 30th of December.

Screenshot 2025-12-19 131315.png

Paul Newcome

@jedelman What is the exact formula you are using that you would expect to check the boxes for the 28th, 29th, and 30th?

jedelman

=IFERROR(IF(AND(WEEKNUMBER([Projected Cleaning Date]@row ) = WEEKNUMBER(TODAY(14)), YEAR([Projected Cleaning Date]@row ) = YEAR(TODAY(14))), 1), "")

Paul Newcome

Give this a try:
=IF([Projected Cleaning Date]@row + (2 - WEEKDAY([Projected Cleaning Date]@row)) - IF(WEEKDAY([Projected Cleaning Date]@row) < 2, 7, 0) = TODAY(14) + (2 - WEEKDAY(TODAY(14))) - IF(WEEKDAY(TODAY(14)) < 2, 7, 0), 1)

What we do is go to the Monday before the [Projected Cleaning Date] (if that date is a Monday, we just use that date). We then do the same with TODAY(14). If those two dates are the same, it will check the box. For 3 weeks out, use 21 in each of the three TODAY() functions, so on and so forth. Basically we standardize the cleaning date and today to Mondays and then compare them.

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.