Changing WEEKNUM formula to pull on Sunday's instead of Monday's?

BCK Team · 2020-12-21T22:06:49+00:00

There was an error rendering this rich post.

Hello,

I have a sheet that acts as a "day tracker." Each row is a new date that tracks specific data in each column. I have a "Week Number" column attached to the "date" column, however the week number populated does not match the week number shown in Outlook calendar and Excel. I think this is because Smartsheets starts their week on Monday's and other programs start the week on Sunday's.

Does anyone have a work around for getting the WEEKNUM formula to populate on Sunday's instead of Mondays? I have tried changing the sheet to *Gantt View* and changing the "Start day for the Week" to Sunday but that still does not impact the WEEKNUM formula outcome.

Example:

In Smartsheets:

12/27/20 = Week Number 52

12/28/20 = Week Number 53

In Excel:

12/27/20 = Week Number 53

12/28/28 = Week Number 53

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Stacy Baughman

After reading through all the info in this thread, there is one piece of critical information that is missing that will remedy this entire issue.

The last year that had 53 weeks on the calendar was 2016, before that was 2011; the next year to have 53 weeks on the calendar is 2022, followed by 2028.

The year 2020 should never have registered a week 53.

Please address this issue directly in the base software. Customers should not need to implement formula work-arounds for this issue - it will only cause further issues down the road when the calendar year flips again - and in 2022 you WILL want a week 53 😉

https://community.smartsheet.com/discussion/comment/273032#Comment_273032

Thank you @Leibel S. This worked perfect for me as I had the same question. WEEKNUM will now use Sunday - Saturday instead of Monday - Sunday if I just add one day to the date as the formula you suggested. See below:

@BCK Team - check out the above chart. I put in a few dates to see if it would work for your application as well.

BCK Team

L@123

Thank you so much for the detailed breakdown! I seriously appreciate the effort and time that took.

Tried the formula and now I am getting this:

I used this formula: ([Todays Date] is the cell being referenced that has the date within it)

=ROUNDDOWN(NETDAYS(DATE(YEAR([Todays Date]10), 1, 1) - WEEKDAY(DATE(YEAR([Todays Date]10), 1, 1)) + 1, [Todays Date]10) / 7)

Any thoughts on why it is not aligning?

L_123

Not a problem BCK. I'll do a quick breakdown of how it works, and how it can be adjusted for different applications. TLDR at the bottom.

Objective: Find the week number starting from the sunday falling on the first week of january to a specified date.

Strategy: Find the number of days between the specified date and the sunday falling on the first week of january, divide the result by 7, and round the result down to get the number of weeks between the dates.

Solution:

Whole Formula:

=ROUNDDOWN(NETDAYS(DATE(YEAR(TODAY()), 1, 1) - WEEKDAY(DATE(YEAR(TODAY()), 1, 1)) + 1, TODAY()) / 7)

Broken Down into Steps:

A. =ROUNDDOWN(

B. NETDAYS(

C. DATE(YEAR(TODAY()), 1, 1) -

D. WEEKDAY(DATE(YEAR(TODAY()), 1, 1))

E. + 1,

F. TODAY()

G. ) / 7)

First we need to find the sunday falling on the first week of january. To do this we find the first day in january [C.]. Then we figure out what day of the week it falls under, and convert it to an integer using the weekday formula [D.]. After subtracting the two, the result will be the saturday of the week prior to the first week in january, and as we are looking for sunday, we simply need to add one day to the result [E.]

Now we have the sunday falling on the first week of january. We need to tell the formula the end of the date range, so in this case we used the current date [F.]. We put the sunday falling on the first week of january as the start date of a netdays formula, and the current date as the end date of the netdays formula to get the number of days between [B. -> F.].

Now that we have the number of days between our two specified dates represented as an integer, we can find the number of weeks that can fit into that range by dividing by 7 [G.] and rounding the result down [A.] for our answer.

Note this solution will not work for the few days in december that fall on the first week of january, we can add some conditional logic at the start of this formula to fix that if you want. Just let me know if that is an issue for you.

To make this formula dynamic, you simply need the single date you want to find the weeknumber for, and replace all references of TODAY() in the proposed solution with that cell reference. You don't need any helper columns.

TLDR: Its the days between the given date and the sunday of the first week of january, divided by 7, then rounded down. Replace all references of TODAY() in the proposed formula with a reference to a cell containing a date to get the weeknumber of said date.

BCK Team

@L@123 I like this idea. I am having some difficulties understanding how to re-create this formula though.

I am no formula expert. I tried applying your formula to my sheet and it is appearing broken.

I have a "today's date" column that the week number is being pulled off of. Will I need another date column that this formula references?

Sorry for the newbie questions - thanks for the guidance!

Paul Newcome

@L@123 That's along the lines of what I was thinking. Cutting out the WEEKNUMBER functions and just looking at days.

L_123

I think you are looking at this problem from the incorrect perspective by using conditional logic in combination with the weeknumber formula. You can achieve more dynamic and personalized results similar to what you see in excel by starting from scratch, and dividing the day you wish to start to the target date by 7. (in the case of the formula below is the sunday falling on the first week of january of the current year until the current date)

=ROUNDDOWN(NETDAYS(DATE(YEAR(TODAY()), 1, 1) - WEEKDAY(DATE(YEAR(TODAY()), 1, 1)) + 1, TODAY()) / 7)

Leibel S

@BCK Team

The Excel formula actually has many options and you can have it start from any day of the week.

That being said, if you do want the WEEKUM in Smartsheet to start on any day of the week simply add to the WEEKUM formula. For example in the below formula I added +1 to my current date, this way if the [Date]@row falls out on a Sunday it will calculate the week based on the next date (which in Smartsheet would you move up one week).

=WEEKNUMBER([Date]@row + 1)

Paul Newcome

So let me make sure I understand correctly...

Week 1 will ALWAYS be whatever week Jan 1 falls in and you are using weeks from Sun - Sat instead of the built in SS Mon - Sun?

Kelly Moore

Hey @BCK Team

I didn't want you to think I had stopped working. Here's what I have so far. We can easily manage weeknumbers for 2021- which is initially what I thought was needed. However making the formula robust enough for any year, at least on the path I started, is complicated. The community is full of clever members and I'll shout out to a few that I have seen do very ingenious things specifically with dates- not intentionally leaving anyone out. @L@123 , @Leibel S , @Genevieve P, @Paul Newcome,@MCorbin . You're in luck that @Mark Cronk is already weighing in. I always welcome learning/ building with others.

As I looked at Outlook weeknumbers vs Smartsheet weeknumbers, here's what I observed

As you said, Outlook uses Sun as Day 1. I added a helper column called Adj Day to look for Sundays, and added a 1 when it was a Sunday, zero for everything else. This eliminated having to always check within the formula for Sundays, which on my path saved alot of IFs.

My [ADJ Day] = IF(WEEKDAY([Start Date]@row) = 1, 1, 0) (This is a Text/Number column)

Your original request becomes:

=IF(WEEKNUMBER([Start Date]@row + [ADJ Day]@row) > 52, WEEKNUMBER([Start Date]@row + [ADJ Day]@row) - 52, WEEKNUMBER([Start Date]@row + [ADJ Day]@row))

For me, the harder part is

Outlook weeknumbers always stop at 52

Outlook makes whatever week Jan1 falls in as week1, regardless of what day of the week it is. This requires the Smartsheet Adjusted Weeknumbers be offset anywhere from 0 - 2 weeks to match with Outlook.

When Jan1 is a Sunday, particularly if it is a Sunday on Smartsheet week 52 (smartsheet 1/1/23), that's a special case- At least for the path I was heading, which is where I stopped. Perhaps as I continue to close my dangling False's some of the issues I see now will resolve themselves. Certainly though, my solution will become more complex so I'm hoping the community can offer you a cleaner solution. My current path broken down for easier reading- I've probably missed something easy that would uncomplicate it:

Here is the above so you don't have to re-type it.

=IF(WEEKNUMBER([Start Date]@row + [ADJ Day]@row) > 52, WEEKNUMBER([Start Date]@row + [ADJ Day]@row) - 52, IF(WEEKNUMBER(DATE(YEAR([Start Date]@row), 1, 1)) = 1, WEEKNUMBER([Start Date]@row + [ADJ Day]@row), IF(WEEKNUMBER(DATE(YEAR([Start Date]@row), 1, 1)) > 52, WEEKNUMBER([Start Date]@row + [ADJ Day]@row) + (WEEKNUMBER(DATE(YEAR([Start Date]@row), 1, 1)) - 52), IF(WEEKNUMBER(DATE(YEAR([Start Date]@row), 1, 1)) = 52, IF(WEEKNUMBER([Start Date]@row + [ADJ Day]@row) = 52, 1, (WEEKNUMBER([Start Date]@row + [ADJ Day]@row) + 53) - 52)))))

cheers,

Kelly

Mark Cronk

Hi @BCK Team ,

Try:

=IF([week number]@row+1>=52, 52, IF(WEEKDAY([Start Day]@row) = 1, [Week Number]@row + 1, [Week Number]@row))

Work?

Mark

BCK Team

Thank you so much for responding!

I tried the formula you suggested and I am now getting this:

It is still not matching the Excel/Outlook Week Numbers - Week # 1 start date should be 12/27/20.

Do you have any other thoughts?

Kelly Moore

You could consider adding a helper column. I will call it ADJ WeekNumber.

=IF(WEEKDAY([Start Day]@row) = 1, [Week Number]@row + 1, [Week Number]@row)

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.