Help with WEEKNUMBER formula

Xochitl C.

Hi, I'm looking for some help with a formula.

I need to create a formula in a column named "Period" with the #of the week and month from a system column (modified date). The format I need to use is a combined text with the number of the week and the month, for example "WK 1 Jan", "WK 2 Jan", etc.

I tried: =WEEKNUMBER([Audit Submission Date]@row) - WEEKNUMBER(DATE(YEAR(Timestamp@row), MONTH(Timestamp@row), 1)) + 1 but this gives me an error

I also have tried several formulas without any luck. Can someone help please?

Here is a screenshot for reference. Many thanks in advance!

Kelly Moore

Hello @Xochitl C.

I'm happy to help. To help me clarify - were you expecting the formula above to produce the combined week number and month text string, or was that formula meant to do something else?

To produce the combined text, try this. As written below, it will produce WK 1 JAN2024, which might be needed if your sheet will span multiple years

="WK" + " " + WEEKNUMBER([Audit Submission Date]@row) + " " + IF(MONTH([Audit Submission Date]@row) = 1, "JAN", IF(MONTH([Audit Submission Date]@row) = 2, "FEB", IF(MONTH([Audit Submission Date]@row) = 3, "MAR", IF(MONTH([Audit Submission Date]@row) = 4, "APR", IF(MONTH([Audit Submission Date]@row) = 5, "MAY", IF(MONTH([Audit Submission Date]@row) = 6, "JUN", IF(MONTH([Audit Submission Date]@row) = 7, "JUL", IF(MONTH([Audit Submission Date]@row) = 8, "AUG", IF(MONTH([Audit Submission Date]@row) = 9, "SEP", IF(MONTH([Audit Submission Date]@row) = 10, "OCT", IF(MONTH([Audit Submission Date]@row) = 11, "NOV", IF(MONTH([Audit Submission Date]@row) = 12, "DEC")))))))))))) + YEAR([Audit Submission Date]@row)

If you to not need the year as part of the textstring, delete the bold text from the formula above.

Will this work for you?

Kelly

Kelly Moore

Hello @Xochitl C.

I'm happy to help. To help me clarify - were you expecting the formula above to produce the combined week number and month text string, or was that formula meant to do something else?

To produce the combined text, try this. As written below, it will produce WK 1 JAN2024, which might be needed if your sheet will span multiple years

="WK" + " " + WEEKNUMBER([Audit Submission Date]@row) + " " + IF(MONTH([Audit Submission Date]@row) = 1, "JAN", IF(MONTH([Audit Submission Date]@row) = 2, "FEB", IF(MONTH([Audit Submission Date]@row) = 3, "MAR", IF(MONTH([Audit Submission Date]@row) = 4, "APR", IF(MONTH([Audit Submission Date]@row) = 5, "MAY", IF(MONTH([Audit Submission Date]@row) = 6, "JUN", IF(MONTH([Audit Submission Date]@row) = 7, "JUL", IF(MONTH([Audit Submission Date]@row) = 8, "AUG", IF(MONTH([Audit Submission Date]@row) = 9, "SEP", IF(MONTH([Audit Submission Date]@row) = 10, "OCT", IF(MONTH([Audit Submission Date]@row) = 11, "NOV", IF(MONTH([Audit Submission Date]@row) = 12, "DEC")))))))))))) + YEAR([Audit Submission Date]@row)

If you to not need the year as part of the textstring, delete the bold text from the formula above.

Will this work for you?

Kelly

Xochitl C.

https://community.smartsheet.com/discussion/comment/412210#Comment_412210

Thanks SO much Kelly!!! This is exactly what I needed.