WEEK # in Month Calculation

Dakota Haeffner

Hi there,

I'm using this formula to calculate what week number in a month a week is. For example 1/1/24 would be week 1 and 1/7 would be week 2, 1/14 starts week three etc. I'm getting 1/14 coming through as week 2. Any suggestions or ideas on how I could clean up this formula to function properly?

=WEEKNUMBER(Date@row) - WEEKNUMBER(DATE(YEAR(Date@row), MONTH(Date@row), 1)) + 1

Paul Newcome

All we need is an IF to say that if the weekday is a Sunday (1) then add one more to the final calculation.

=WEEKNUMBER(Date@row) - WEEKNUMBER(DATE(YEAR(Date@row), MONTH(Date@row), 1)) + 1 + IF(WEEKDAY(Date@row) = 1, 1, 0)

DKazatsky2

Hi @Dakota Haeffner,

Internally, Smartsheet uses Monday as the first day of the week, that is why you are getting these results.

Hope that helps,

Dave

Dakota Haeffner

@DKazatsky2,

Help to know thank you! Seems like a strange way to do it.

Hoping someone might be able to give me idea for a solution oriented formula.

Ryan Kramer

Hi,

I understand that you are trying to calculate the week number in a month based on a given date. It seems like there might be an issue with the formula you provided.

Here's an updated formula that should help you achieve the desired result:

=WEEKNUM(Date@row, 2) - WEEKNUM(DATE(YEAR(Date@row), MONTH(Date@row), 1), 2) + 1

Please give this formula a try and let me know if it works correctly for you. If not, please provide some sample dates and their expected week numbers so that I can further assist you.

Best regards,

Ryan Kramer

Have you backed up your solution lately? https://www.smartsheetbackups.com/

Andrée Starå

Hi @Dakota Haeffner

I hope you're well and safe!

To add to Ryan's excellent advice/answer.

It seems like Ryan was in Excel mode.

Here's the Smartsheet version.

=WEEKNUMBER(Date@row, 2) - WEEKNUMBER(DATE(YEAR(Date@row), MONTH(Date@row), 1), 2) + 1

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

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

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

Dakota Haeffner

@Andrée Starå @Ryan Kramer

I appreciate the collaboration and effort guys!

Unfortunately, this still returned week 2 for 1/14/24. 😫

=WEEKNUMBER([Event Date]@row, 2) - WEEKNUMBER(DATE(YEAR([Event Date]@row), MONTH([Event Date]@row), 1), 2) + 1

Dakota Haeffner

@Ryan Kramer

Sample dates look like it would be any Sunday this year.

1/14/24 should return Week 3, but it shows Week 2,

1/21/24 should return Week 4, but it shows Week 3,

1/28/24 should return Wekk 5, but it shows Week 4, etc..

Paul Newcome

All we need is an IF to say that if the weekday is a Sunday (1) then add one more to the final calculation.

=WEEKNUMBER(Date@row) - WEEKNUMBER(DATE(YEAR(Date@row), MONTH(Date@row), 1)) + 1 + IF(WEEKDAY(Date@row) = 1, 1, 0)

Dakota Haeffner

@Paul Newcome GOAT thank you Paul!

Paul Newcome

Happy to help. 👍️

DKazatsky2

Hi @Paul Newcome,

Can you explain the significance of this portion of the equation - "=WEEKNUMBER(Date@row) - WEEKNUMBER(DATE(YEAR(Date@row), MONTH(Date@row), 1)) + 1".

Doesn't that provide the same value as "=WEEKNUMBER(Date@Row)"?

If the formula is shortened to "=WEEKNUMBER(Date@row) + IF(WEEKDAY(Date@row) = 1, 1, 0)" it seems to give the desired outcome (utilizing your IF suggestion).

Any insight as to why the longer formula is recommended/preferred would be appreciated.

Paul Newcome

@DKazatsky2 The formula is intended to provide the week number within the month. Your formula will provide the week number within the year.

DKazatsky2

@Paul Newcome

Ahhhh, I completely missed that distinction. Thanks very much for the reply.

MPath

https://community.smartsheet.com/discussion/comment/419548#Comment_419548

Nice.

Thanks for this.