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?

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    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

  • Carson Penticuff
    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
    Paul Reeves ✭✭✭✭✭

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

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    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"))))))))

  • Paul Reeves
    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).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!