Date formula question

Connie Cochran

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?

MichaelTCA

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.

MichaelTCA

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.