# WEEK # in Month Calculation

✭✭✭✭✭

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

Tags:

• ✭✭✭✭✭✭

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)

• ✭✭✭✭✭✭

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

Hope that helps,

Dave

• ✭✭✭✭✭

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.

• ✭✭✭✭✭

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/

• ✭✭✭✭✭✭
edited 02/13/24

I hope you're well and safe!

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭

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

• ✭✭✭✭✭

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..

• ✭✭✭✭✭✭

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)

• ✭✭✭✭✭

@Paul Newcome GOAT thank you Paul!

• ✭✭✭✭✭✭

Happy to help. 👍️

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

• ✭✭✭
edited 06/05/24

Nice.

Thanks for this.

Continuous Improvement Facilitator in HVAC industry || Timezone GMT +1

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!