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
Check out the Formula Handbook template!