This post represents a public cross-listing of a post I made to the Consulting Organization at Smartsheet in Feb 2019. The purpose is to help out potential end-users reading this. All thoughts are my own.

I’ve come up with a useful formula that can be used to dynamically calculate the date of the Sunday, given a week:

=Date@row - (WEEKDAY(Date@row) - 1)

Breaking this down, the Date@row is the date given to calculate.  This can be any date given this week.  For example, today is Tuesday, the 19th.  

The subtraction subtracts the quantity of the weekday of today (in this example it’s 3) from the date today.  To get Sunday (which is the first day of the week), we need to subtract two days from this. Thus, there is an additional – 1 for Sunday.  If you need to calculate other days, we will need to shift the – X.  For example, for Monday of the week, we will need to -2 from WEEKDAY(Date@row). 

This is useful in a number of ways, but primarily, I’ve used it in two cases.

Weekly / Monthly Status Log

If a client needs to do status tracking and have a visible historical, we can create a log that automatically inputs a list of days they need to fill in the statuses.  This plays nicely with alerts and actions, as we can drive an update via the dates.  This also mitigates the use of the today() function, which has been traditionally problematic, while allowing us to have a dynamic status update requests and mitigating manual work.  In this example screenshot, we have a current month (manual, allows for selection of display status), a month of the project, the date related to that month (i.e. first Sunday of the month), and the status update.  We surface this to both the Project Dashboard and the master roll up as metadata, but this works with an update request to the Project Manager. 

Cycle Time (Weeks)

If you want to compute cycle time by weeks, this is one way to do it (number of Sundays elapsed).  This is a workaround for our not having a modulo and round function, since round can go up or down.  If you approximate the weeks via Sundays, you will always get a number that is a multiple of 7.  This cycle time measure is useful for project types that are longer, as I’ve heard clients say that 285 or 390 is such an abstract number, whereas 40 weeks or 56 weeks is a nicer way of doing so.  This also helps create consistency with Gantt charting that doesn’t go by days.