Help with WEEKNUMBER formula

amber.lange

Hi, I need a column to show the week number of the year. When I try the simple =weeknumber(TimeCardEntryDate@row) formula, it doesn't seem correct, but I've seen other posts about how Smartsheet calculates week numbers uniquely. I need the weeks to count from Sunday-Saturday. So, in the picture below, 12/31/23-1/6/24 should all be the same week number.

Also, we want Week 1 to be the first full week of January. I'll need to account for the calendar year because this list might span over a few years.

I thought I've asked this question before or at least seen another discussion on it but cannot find anything today to help solve this :(

KPH

Hi @amber.lange

Part 1 - Sunday to Saturday

Smartsheet WEEKNUMBER calculates week numbers based on Mondays.

So if you want to calculate based on Sunday you could adjust the formula to add 1 day to your date (making Sunday a Monday).

=WEEKNUMBER(TimeCardEntryDate@row + 1)

Part 2 - Year start

Smartsheet WEEKNUMBER does the following:

• If January 1 is a Monday, WEEKNUMBER returns 1 
• If January 1 is a Friday, WEEKNUMBER returns 52 (or 53, if there are 53 weeks in a year)

So as you are adding 1 to your dates Dec 31st 2023 is week 1 of 2024. Which I don't think you want. Do you want 12/31/23-1/6/24  to be week 0, or week 52/53?

amber.lange

Hi @KPH

12/31/23-1/6/24 would be week 53.

Thanks in advance for this help!

KPH

I was worried you would say that (and hoping you wouldn't!)

To number the weeks with week 1 starting on the first Sunday of January we are going to need combine a few formula. I will talk you through each one in turn, putting the new parts in bold as the formula builds. I suggest you enter these one step at a time so you can troubleshoot any issues and see what is happening. Note that in step 1 the column needs to be a Date type, after this it is Text.

(I'm afraid I rushed this a little as my laptop battery is about to die. Please ask any follow up questions you need).

1. Find the first Sunday of the year.

=DATE(YEAR(TimeCardEntryDate@row), 1, 7) - (WEEKDAY(DATE(YEAR(TimeCardEntryDate@row), 1, 7)) - 1)

This looks at the year in TimeCardEntryDate and finds the first Sunday for the year in that date.

2. Calculate the number of days between the first Sunday and the date in the row.

Use the output of formula 1 in this NETDAYS formula to find the days between the TimeCardEntryDate and the 1st Sunday that we identified in step 1.

=NETDAYS(DATE(YEAR(TimeCardEntryDate@row), 1, 7) - (WEEKDAY(DATE(YEAR(TimeCardEntryDate@row), 1, 7)) - 1), TimeCardEntryDate@row)

3. Subtract 1

As this is Net Days I subtracted 1 from the output.

=NETDAYS(DATE(YEAR(TimeCardEntryDate@row), 1, 7) - (WEEKDAY(DATE(YEAR(TimeCardEntryDate@row), 1, 7)) - 1), TimeCardEntryDate@row) - 1

4. Calculate the weeks since the first Sunday of the year.

We divide the output of formula 3 by 7 (7 days per week), round this down to the nearest whole number to find the number of weeks.

=ROUNDDOWN((NETDAYS(DATE(YEAR(TimeCardEntryDate@row), 1, 7) - (WEEKDAY(DATE(YEAR(TimeCardEntryDate@row), 1, 7)) - 1), TimeCardEntryDate@row) - 1) / 7)

This gives us the number of (Sunday starting) full weeks since the since the first Sunday of the year for each date in TimeCardEntryDate.

5. Add one to this number as we want the week that is 0 weeks after the 1st Sunday to be week 1

=ROUNDDOWN((NETDAYS(DATE(YEAR(TimeCardEntryDate@row), 1, 7) - (WEEKDAY(DATE(YEAR(TimeCardEntryDate@row), 1, 7)) - 1), TimeCardEntryDate@row) - 1) / 7) +1

6. Handle the days before the first Sunday of the year.

The formula in step 5 works for all dates except those between the end of the year and the first Sunday of the new year. These dates will return 0 or 1 in formula 5. So we need a different formula for those.

Fortunately, it is easy to find our "problem" rows as these are the ones where the number of days since the 1st Sunday is a negative number.

We can start an IF function like this.

=IF(NETDAYS(DATE(YEAR(TimeCardEntryDate@row), 1, 7) - (WEEKDAY(DATE(YEAR(TimeCardEntryDate@row), 1, 7)) - 1), TimeCardEntryDate@row) - 1 < 0, "new formula", "current formula")

This says if the output from step 3 is negative, the date in TimeCardEntryDate is before the first Sunday, and therefore "new formula" will be displayed. For all other dates "current formula is displayed".

7. Add formula in 5 to the IF in 6.

We can paste the formula that works for >0 into the IF as shown in bold here (I left it out in step 6 so you can see how the IF works).

=IF(NETDAYS(DATE(YEAR(TimeCardEntryDate@row), 1, 7) - (WEEKDAY(DATE(YEAR(TimeCardEntryDate@row), 1, 7)) - 1), TimeCardEntryDate@row) - 1 < 0, "new formula", ROUNDDOWN((NETDAYS(DATE(YEAR(TimeCardEntryDate@row), 1, 7) - (WEEKDAY(DATE(YEAR(TimeCardEntryDate@row), 1, 7)) - 1), TimeCardEntryDate@row) - 1) / 7) +1)

8. Create a new formula for the days before the first Sunday of the year.

This formula will add the days before the first Sunday to the end of the previous year (it assumes a 365 day year but I think we'll be OK with leap years due to the rounding element). It basically adds 8 days to the end of the year then subtracts (by adding a negative) the number of days before the first Sunday.

= 365 + 8 + NETDAYS(DATE(YEAR(TimeCardEntryDate@row), 1, 7) - (WEEKDAY(DATE(YEAR(TimeCardEntryDate@row), 1, 7)) - 1), TimeCardEntryDate@row) - 1

9. Calculate the number of weeks since the first January of the previous year for the days in the next year that are before the 1st Sunday

Similar the the formula in step 5.

=ROUNDDOWN((365 + 8 + NETDAYS(DATE(YEAR(TimeCardEntryDate@row), 1, 7) - (WEEKDAY(DATE(YEAR(TimeCardEntryDate@row), 1, 7)) - 1), TimeCardEntryDate@row) - 1) / 7) + 1

10. Add the formula in step 9 into the IF formula in step 7.

The new formula here replaces the "new formula" in the IF statement

=IF(NETDAYS(DATE(YEAR(TimeCardEntryDate@row), 1, 7) - (WEEKDAY(DATE(YEAR(TimeCardEntryDate@row), 1, 7)) - 1), TimeCardEntryDate@row) - 1 < 0, ROUNDDOWN((365 + 8 + NETDAYS(DATE(YEAR(TimeCardEntryDate@row), 1, 7) - (WEEKDAY(DATE(YEAR(TimeCardEntryDate@row), 1, 7)) - 1), TimeCardEntryDate@row) - 1) / 7) + 1, ROUNDDOWN((NETDAYS(DATE(YEAR(TimeCardEntryDate@row), 1, 7) - (WEEKDAY(DATE(YEAR(TimeCardEntryDate@row), 1, 7)) - 1), TimeCardEntryDate@row) - 1) / 7) + 1)

Example:

Miss a few...