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
-
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.
Answers
-
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?
-
Take a look through THIS THREAD. I believe there is a solution for what you are wanting to do.
-
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
-
-
Sorry about that. Work has been pretty busy. I have a sheet set up to do some testing when I am able.
-
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.
-
Hi Paul,
You are an absolute legend!!!!!!!! Thank you so much! Works exactly how I wanted it to.
-
Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives