Looking to capture the week# within a month

Paul Reeves

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?

Carson Penticuff

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 Penticuff

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.

Paul Reeves

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

Carson Penticuff

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

Paul Reeves

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