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.
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!
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?
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!
-
Take a look through THIS THREAD. I believe there is a solution for what you are wanting to do.
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!
-
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.
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!
-
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!
-
Hi Paul,
You are an absolute legend!!!!!!!! Thank you so much! Works exactly how I wanted it to.
-
Happy to help. 👍️
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!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives