Formula for determining the week

Hi Everyone,


I have a grid in which all my truck data goes in every day. For the data that goes into that grid everyday, I have created a column in which I want it to tell me what week of the month it is.

I would also like my weeks to work from Sunday to Saturday.


So basically for June it shouldve been as follows:

1-6th = week 1

7-13th = week 2

etc


and for July it should've been as follows:

1-4th = week 1

5-11th = week 2


Attached below is a simple excel file of how my grid looks. It should help you get a better understanding.

So essentially I need help with a formula to go into the orange strip, and then someone to please help me automate the table to the right as well


Regards

Rainier

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I made a slight modification to the solution in the above linked thread.

    =WEEKNUMBER(Date@row) - WEEKNUMBER(DATE(YEAR(Date@row), MONTH(Date@row), 1)) + 1 + IF(WEEKDAY(Date@row) = 1, 1)


    Give that a go and see how it works.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers