Date formula question
If I have a field with the date of 8/9/2023 and I want to add a column with a formula to find the date for the beginning of that week how would I write it?
Best Answer
-
Hello @Connie Cochran
You could use the week number as a criteria to specify the week and the MIN function to find the "oldest" date past out of the set of data. In your case, it would be the beginning of the week.
=MIN(COLLECT([Date Column]:[Date Column], [Date Column]:[Date Column], WEEKNUMBER([Date Column]@row)=WEEKNUMBER(TODAY())
This will reference only the set of data and will not input the actual date of the start of the week.
Ex: If your data set has 8/23, 8/24, and 8/25. The result from this function would be 8/23 because it's the earliest day mentioned out of the set of data. It will not show 8/20 which was the actual first day of the week.
Answers
-
Hello @Connie Cochran
You could use the week number as a criteria to specify the week and the MIN function to find the "oldest" date past out of the set of data. In your case, it would be the beginning of the week.
=MIN(COLLECT([Date Column]:[Date Column], [Date Column]:[Date Column], WEEKNUMBER([Date Column]@row)=WEEKNUMBER(TODAY())
This will reference only the set of data and will not input the actual date of the start of the week.
Ex: If your data set has 8/23, 8/24, and 8/25. The result from this function would be 8/23 because it's the earliest day mentioned out of the set of data. It will not show 8/20 which was the actual first day of the week.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 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
- 287 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!