# Dates returned for previous Sunday

#### caj1980

##### Board Regular
I am looking for a formula that will return the date of the previous Sunday and a second formula that will return the date of the Sunday before that (two Sundays ago).

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### caj1980

##### Board Regular
FOund this online which works for the previous Sunday, but unsure how to update it for the Sunday 2 weeks ago?

=INT((TODAY()-1)/7)*7+1

#### Rick Rothstein

##### MrExcel MVP
FOund this online which works for the previous Sunday, but unsure how to update it for the Sunday 2 weeks ago?

=INT((TODAY()-1)/7)*7+1
Try subtracting 7 from that formula...

=INT((TODAY()-1)/7)*7-6

Although I would not use that formula, rather, just subtract 7 from whatever cell you put the formula you found in. So, if you put it in cell, say, C2, then the Sunday 2 weeks ago would be...

=C2-7

Last edited:

#### Tetra201

##### MrExcel MVP
For previous Sunday: =TODAY()-WEEKDAY(TODAY(),2)
For Sunday before that: =TODAY()-7-WEEKDAY(TODAY(),2)

#### caj1980

##### Board Regular

ADVERTISEMENT

Try subtracting 7 from that formula...

=INT((TODAY()-1)/7)*7-6

Although I would not use that formula, rather, just subtract 7 from whatever cell you put the formula you found in. So, if you put it in cell, say, C2, then the Sunday 2 weeks ago would be...

=C2-7

THis works great, thanks so much
=INT((TODAY()-1)/7)*7-6

#### caj1980

##### Board Regular
For previous Sunday: =TODAY()-WEEKDAY(TODAY(),2)
For Sunday before that: =TODAY()-7-WEEKDAY(TODAY(),2)

This works great, too. I like the logic of this formula better than the other so I will be using this one. Thanks!

ADVERTISEMENT

You are welcome.

#### Rick Rothstein

##### MrExcel MVP
For previous Sunday: =TODAY()-WEEKDAY(TODAY(),2)
For Sunday before that: =TODAY()-7-WEEKDAY(TODAY(),2)
This works great, too. I like the logic of this formula better than the other so I will be using this one. Thanks!
I was going to post that formula, but decided against it because you said the first formula worked the way you want. Why did I decide that? I did not want to get into the fact that he above formula returns a different value if "TODAY()" is Sunday. Now, if you will never look at your worksheet on a Sunday, then either formula is fine to use, but if you could look at it on a Sunday, you have to decide if the first formula worked for you or not.... if so, then you cannot replace it with the second formula without modifying it slightly.

#### Tetra201

##### MrExcel MVP
...I did not want to get into the fact that the above formula returns a different value if "TODAY()" is Sunday.
@Rick Rothstein:

I constructed it this way intentionally: if today is a Sunday, then the previous Sunday was, well, the previous Sunday, not today.

Last edited:

#### Rick Rothstein

##### MrExcel MVP
@Rick Rothstein:

I constructed it this way intentionally: if today is a Sunday, then the previous Sunday was, well, the previous Sunday, not today.
I know... and as I said, I was going to post it as well, but the OP said the formula he posted did what he wanted, so I decided not to post it because of the different treatment for Sundays.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,167,816
Messages
5,855,798
Members
431,765
Latest member
RedleoUK

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back