Date formula question

Options

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

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    Answer ✓
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!