Suppose you wanted to create a weekly agenda spreadsheet in which you not only wanted the days of the week across the columns, but the calendar dates as well (i.e. Tuesday, October 16, 2012 would appear as Tuesday (16).)  But rather than having to update the dates each week, you want to make them dynamic so that they would always show the correct dates.

Suppose you wanted to create a weekly agenda spreadsheet in which you not only wanted the days of the week across the columns, but the calendar dates as well (i.e. Tuesday, October 16, 2012 would appear as Tuesday (16).)  But rather than having to update the dates each week, you want to make them dynamic so that they would always show the correct dates.

You know that you can use =TODAY() to get today’s date.

But how can you determine the date for Sunday of the current week?

Let’s use October 16, 2012 for an example.

There are two functions that you will need to use:  TODAY() and WEEKDAY().

To get the date for the Sunday of the current week, use the following formula . . .

=TODAY()-WEEKDAY(TODAY())+1

To understand what this formula is accomplishing:  TODAY() gives you today’s date (in this case October 16, 2012) and WEEKDAY() returns the weekday number (where Sunday = 1, Monday = 2, Tuesday = 3 . . . . etc.).

Therefore =WEEKDAY(TODAY()) returns the week day of today.

For October 16, 2012, the weekday would be 3 (Tuesday).

When you take today’s date and subtract today’s weekday you get the date for the previous Saturday. (i.e. Tuesday, October 16, 2012 – 3 gives you Saturday, October 13, 2012).

Now, simply add 1 to get Sunday’s date:  =TODAY()-WEEKDAY(TODAY())+1

If you prefer to start with Monday’s date, simply add 2 to the formula rather than 1:
=TODAY()-WEEKDAY(TODAY())+2