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

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

@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)

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

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.

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.