Current Week and Next Week Formula

Hi, I need help with a formula that will help me determine if a date is the current week or the next week in the calendar. Need the weeks to star on Sundays. I'm using the following two formulas:

For Current Week I'm using the following formula, which is working fine:

=IF(AND(YEAR(TODAY()) = YEAR(ChangeDate@row), WEEKNUMBER(TODAY()) = WEEKNUMBER(ChangeDate@row + 1)), "CurrentWeek")

For Next Week I'm using the following formula:

=IF(AND(YEAR(TODAY()) = YEAR(ChangeDate@row), WEEKNUMBER(TODAY() + 1) + 1 = WEEKNUMBER(ChangeDate@row)), "NextWeek")

The problem is that the Next Week formula is pushing the days by 1. For example: 4/18/21 is the Sunday starting week 16. However, the formula is skipping this day and picking 4/19/21 as the first day of the week.

Find more posts tagged with

Accepted answers

Cesar Perez

I read this post https://community.smartsheet.com/discussion/65416/weeknumber-and-weekday and solved my question. Here are the formulas in case anyone can use them.

Last Week Formula

=IF(WEEKDAY([Change Date]@row) = 1, IF(AND(YEAR(TODAY()) = YEAR([Change Date]@row), WEEKNUMBER(TODAY()) - 2 = WEEKNUMBER([Change Date]@row)), "Last Week"),

IF(WEEKDAY([Change Date]@row) > 1, IF(AND(YEAR(TODAY()) = YEAR([Change Date]@row), WEEKNUMBER(TODAY()) - 1 = WEEKNUMBER([Change Date]@row)), "Last Week")))

Current Week Formula

=IF(WEEKDAY([Change Date]@row) = 1, IF(AND(YEAR(TODAY()) = YEAR([Change Date]@row), WEEKNUMBER(TODAY()) = WEEKNUMBER([Change Date]@row + 1)), "Current Week"),

IF(WEEKDAY([Change Date]@row) > 1, IF(AND(YEAR(TODAY()) = YEAR([Change Date]@row), WEEKNUMBER(TODAY()) = WEEKNUMBER([Change Date]@row)), "Current Week")))

Next Week Formula

=IF(WEEKDAY([Change Date]@row) = 1, IF(AND(YEAR(TODAY()) = YEAR([Change Date]@row), WEEKNUMBER(TODAY()) = WEEKNUMBER([Change Date]@row)), "Next Week"),

IF(WEEKDAY([Change Date]@row) > 1, IF(AND(YEAR(TODAY()) = YEAR([Change Date]@row), WEEKNUMBER(TODAY()) + 1 = WEEKNUMBER([Change Date]@row)), "Next Week")))

All comments

Andrée Starå

Hi @Cesar Perez

I hope you're well and safe!

Try something like this.

=IF(WEEKNUMBER(ChangeDate@row) = WEEKNUMBER(TODAY()); "CurrentWeek"; IF(WEEKNUMBER(ChangeDate@row) = WEEKNUMBER(TODAY()) + 1; "NextWeek"; ""))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Rajul Khandelwal

Hi Cesar,

If I understood your function correctly then you are mapping current week and next week on the basis of Year(Today()) and WeekNumber(Today). I am not quite getting the use of +1 in your formula. Your formula is currently checking the week number of Today() with the the next day rather than that day itself. I have updated the formula below. Hope it works.

For Current Week I'm using the following formula, which is working fine:

=IF(AND(YEAR(TODAY()) = YEAR(ChangeDate@row), WEEKNUMBER(TODAY()) = WEEKNUMBER(ChangeDate@row ~~+ 1~~)), "CurrentWeek")

For Next Week I'm using the following formula:

=IF(AND(YEAR(TODAY()) = YEAR(ChangeDate@row), WEEKNUMBER(TODAY() ~~+ 1~~) + 1 = WEEKNUMBER(ChangeDate@row)), "NextWeek")

Regards,

Rajul Khandelwal

Cesar Perez

Thank you both for your prompt response. The problem I have is that the WEEKNUMBER function starts the weeks on Mondays. This means that the first day of this week is Monday, 4/19/21. I need it to calculate starting on Sundays or 4/18/21. Our work week follows regular calendar Sunday through Saturday.

Cesar Perez

I read this post https://community.smartsheet.com/discussion/65416/weeknumber-and-weekday and solved my question. Here are the formulas in case anyone can use them.

Last Week Formula

=IF(WEEKDAY([Change Date]@row) = 1, IF(AND(YEAR(TODAY()) = YEAR([Change Date]@row), WEEKNUMBER(TODAY()) - 2 = WEEKNUMBER([Change Date]@row)), "Last Week"),

IF(WEEKDAY([Change Date]@row) > 1, IF(AND(YEAR(TODAY()) = YEAR([Change Date]@row), WEEKNUMBER(TODAY()) - 1 = WEEKNUMBER([Change Date]@row)), "Last Week")))

Current Week Formula

=IF(WEEKDAY([Change Date]@row) = 1, IF(AND(YEAR(TODAY()) = YEAR([Change Date]@row), WEEKNUMBER(TODAY()) = WEEKNUMBER([Change Date]@row + 1)), "Current Week"),

IF(WEEKDAY([Change Date]@row) > 1, IF(AND(YEAR(TODAY()) = YEAR([Change Date]@row), WEEKNUMBER(TODAY()) = WEEKNUMBER([Change Date]@row)), "Current Week")))

Next Week Formula

=IF(WEEKDAY([Change Date]@row) = 1, IF(AND(YEAR(TODAY()) = YEAR([Change Date]@row), WEEKNUMBER(TODAY()) = WEEKNUMBER([Change Date]@row)), "Next Week"),

IF(WEEKDAY([Change Date]@row) > 1, IF(AND(YEAR(TODAY()) = YEAR([Change Date]@row), WEEKNUMBER(TODAY()) + 1 = WEEKNUMBER([Change Date]@row)), "Next 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.