Looking to capture the week# within a month
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
Best Answer

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"))))))))
Answers

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.

In your example, November 6th, I am leaning towards basing it on the Mondays..
paul e. reeves, CBAP
Austin, Texas
Houghton Mifflin Harcourt

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

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
Categories
Check out the Formula Handbook template!