Formula for determining the week

Rainier Hollands

Week.xlsx

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

Paul Newcome

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.

Paul Newcome

This has actually been solved somewhere here in the Community. I just seem to be having trouble locating the thread.

@L@123, weren't you the one to help in the other thread where we were trying to figure out how to display the week of the month?

Paul Newcome

Take a look through THIS THREAD. I believe there is a solution for what you are wanting to do.

Rainier Hollands

@Paul Newcome

Hi Paul, unfortunately that thread doesnt help, as I'm looking at strating my week on a Sunday to a Saturday.

I also then need help in counting the amount of days in that week.

Regards

Rainier

Rainier Hollands

@Paul Newcome

Hi Paul,

I really need help with this. It's relatively urgent for a client.

Paul Newcome

Sorry about that. Work has been pretty busy. I have a sheet set up to do some testing when I am able.

Paul Newcome

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.

Rainier Hollands

@Paul Newcome

Hi Paul,

You are an absolute legend!!!!!!!! Thank you so much! Works exactly how I wanted it to.

Paul Newcome

Happy to help. 👍️