Reversing WEEKNUMBER Function

Hi all,


I have a few values corresponding to different dates, and I would like to group these dates by their respective weeks. What I am thinking of doing is using the WEEKNUMBER function to generate what week they are in, and then grouping them using this number. Additionally however, I would like to group them by the weeks they are in. Moreover I would like for those weeks to be represented by the Friday, and NOT by the Monday or Sunday. Is there any function that brings me close to this point, or any function that does what I am looking to do? Any help would go a long way and be greatly appreciated. Thank you all in advance!

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Muhammad

    You can use an IF statement to check what day of the week your date is. Then if it's a Friday, Saturday, or Sunday, you can add 1 to the WeekNumber so that the week starts on the Friday.

    For example:

    =IF(OR(WEEKDAY(Date@row) = 1, WEEKDAY(Date@row) = 7, WEEKDAY(Date@row) = 6), WEEKNUMBER(Date@row) + 1, WEEKNUMBER(Date@row))


    Then you can use this column to Group in a Report. Is this what you were looking to do?

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Muhammad

    You can use an IF statement to check what day of the week your date is. Then if it's a Friday, Saturday, or Sunday, you can add 1 to the WeekNumber so that the week starts on the Friday.

    For example:

    =IF(OR(WEEKDAY(Date@row) = 1, WEEKDAY(Date@row) = 7, WEEKDAY(Date@row) = 6), WEEKNUMBER(Date@row) + 1, WEEKNUMBER(Date@row))


    Then you can use this column to Group in a Report. Is this what you were looking to do?

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!