"Week of" formula with Friday as start of week

Need the formula below to recognize that the beginning of the week is a Friday. If the "Date of Shift" lands on a Friday, then the "Week of" should be the same date.

=IF(WEEKDAY([Date of Shift]@row) = 7, [Date of Shift]@row - 1, [Date of Shift]@row - (WEEKDAY([Date of Shift]@row) + 1))

Below is a screenshot of the formula in action, working properly except when the shift date lands on a Friday.

In the example below, if the date of shift is 1/17/2025, the "Week of" should be 1/17/2025. Our shifts are tracked Friday through Thursday.

THANK YOU IN ADVANCE!

Tags:

Answers

  • We figured out a work around to the problem above by using a formula for each day of the week individually and writing the formula to display the date we wanted. Here's the updated formula understanding that Sunday is recognized in SmartSheet as day 1:

    Here's the formula in action:

    If you have a more simplified way to condense the formula above its much appreciated!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!