Search code examples
dategoogle-sheetsgoogle-sheets-formulaarray-formulasweekday

How to get last Sunday's date?

I need to show last Sunday's date in a cell for a weekly report that I'm creating on google sheets. I've been googling to find a solution and the closest I found is this:

=TODAY()+(7-WEEKDAY(TODAY(),3))

But this gives next Monday's date. Any idea how to modify this to show last Sunday's date? Alternately, do you have another way to solve this problem?

Solution

  • The formula you're looking for would be:

    =DATE(YY, MM, DD) - (WEEKDAY(DATE(YY, MM, DD)) - 1) - 7

// OR

=TODAY() - (WEEKDAY(TODAY()) - 1) - 7

    Depending on what you take to be "last Sunday," you can simplify/factor this:

    If you take "last Sunday" to mean, "the Sunday which has most recently happened:"

    =DATE(A2,B2,C2) - WEEKDAY(DATE(A2,B2,C2)) + 1

    If you take "last Sunday" to mean, "the Sunday which took place during the previous week:"

    =DATE(A4,B4,C4) - WEEKDAY(DATE(A4,B4,C4)) - 6

    Working through it:

    =TODAY() - (WEEKDAY(TODAY()) - 1) - 7


 TODAY()
 // get today's date, ie. 22/11/2019

            WEEKDAY(TODAY())
            // get the current day of the week, ie. 6 (friday)

         -
         // take the first date and subtract that to rewind through the week,
         // ie. 16/11/2019 (last saturday, since saturday is 0)

                             - 1
                             // rewind back one less than the entire week
                             // ie. 17/11/2019 (this sunday)

                                  - 7
                                  // rewind back to the sunday before this
                                  // sunday, ie. 10/11/2019

    Hopefully this explanation explains what the numbers at the end are doing. + 1 takes you from the Saturday of last week to the Sunday of the current week (what I would call "this Sunday"), and - 6 takes you back through last week to what I would call "last Sunday."

    See here:

    Google Sheets Example