# Looking to capture the week# within a month

Options
✭✭✭✭✭

I am needing to identify a specific week# within the month, for instance 10/16/2023 (US) would be the 3rd week of October. It would need to be based on the Monday of the week, so if the day was 11/1/2023, Wednesday then I would think I need the week to be 5th week of October.

Or does anyone have a different viewpoint?

paul e. reeves, CBAP

Austin, Texas

Houghton Mifflin Harcourt

• ✭✭✭✭✭✭
Options

There is likely an easier (or at least shorter) way to do this, but this seems to work. The source date would be in [Day]

=IF(MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 28)) = MONTH([Day]@row), "5", IF(MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 21)) = MONTH([Day]@row), "4", IF(MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 14)) = MONTH([Day]@row), "3", IF(MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 7)) = MONTH([Day]@row), "2", IF(MONTH(IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2)) = MONTH([Day]@row), "1", IF(MONTH(IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2)) = MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 28)), "5", IF(MONTH(IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2)) = MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 21)), "4", IF(MONTH(IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2)) = MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 14)), "3"))))))))

• ✭✭✭✭✭✭
Options

How would you prefer to define the week number? For example, November 6th is the first Monday of the month, but could be considered the second week, depending on how you approach it.

• ✭✭✭✭✭
Options

In your example, November 6th, I am leaning towards basing it on the Mondays..

paul e. reeves, CBAP

Austin, Texas

Houghton Mifflin Harcourt

• ✭✭✭✭✭✭
Options

There is likely an easier (or at least shorter) way to do this, but this seems to work. The source date would be in [Day]

=IF(MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 28)) = MONTH([Day]@row), "5", IF(MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 21)) = MONTH([Day]@row), "4", IF(MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 14)) = MONTH([Day]@row), "3", IF(MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 7)) = MONTH([Day]@row), "2", IF(MONTH(IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2)) = MONTH([Day]@row), "1", IF(MONTH(IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2)) = MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 28)), "5", IF(MONTH(IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2)) = MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 21)), "4", IF(MONTH(IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2)) = MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 14)), "3"))))))))

• ✭✭✭✭✭
Options

Carson,

Thank you very much. I will definitely try this out. Another approach I have thought about since I asked this question would be the Week of MM/DD, Monday to Friday. At this point I am thinking it being a series of IF statements to test the weekday and subtract to Monday (2).

paul e. reeves, CBAP

Austin, Texas

Houghton Mifflin Harcourt

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!