CODEWITHSUNDEEP
dategoogle-sheetsgoogle-sheets-formulaarray-formulasweekday
Abhay
Abhay

Reputation: 845

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?

Upvotes: 1

Views: 7532

Answers (3)

Kyle
Kyle

Reputation: 1

Using monday as 1 index, this will return the previous sunday or today if it is sunday

=if((7-WEEKDAY(today(),2))>0,today()-(7-(7-WEEKDAY(today(),2))),today()+(7-WEEKDAY(today(),2)))

One can select for other days of the week by changing the number "7" directly before "-WEEKDAY(today(),2)" in the three places that pattern exists.

Upvotes: 0

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(TO_DATE(FILTER(ROW(INDIRECT(VALUE(TODAY()-6)&":"&VALUE(TODAY()))), 
 TEXT(ROW(INDIRECT(VALUE(TODAY()-6)&":"&VALUE(TODAY()))), "ddd")="Sun")))

0

if today is Sunday and you want still the last Sunday then:

=ARRAYFORMULA(IF(TEXT(TODAY(), "ddd")="Sun", TODAY()-6, 
 TO_DATE(FILTER(ROW(INDIRECT(VALUE(TODAY()-6)&":"&VALUE(TODAY()))), 
 TEXT(ROW(INDIRECT(VALUE(TODAY()-6)&":"&VALUE(TODAY()))), "ddd")="Sun"))))

0

Upvotes: 1

matthew-e-brown
matthew-e-brown

Reputation: 3052

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

Upvotes: 5

Related Questions