Dates returned for previous Sunday

caj1980

Board Regular
Joined
Oct 23, 2013
Messages
107
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).
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

caj1980

Board Regular
Joined
Oct 23, 2013
Messages
107
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
Joined
Apr 18, 2011
Messages
36,711
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Oct 14, 2016
Messages
3,639
For previous Sunday: =TODAY()-WEEKDAY(TODAY(),2)
For Sunday before that: =TODAY()-7-WEEKDAY(TODAY(),2)
 

caj1980

Board Regular
Joined
Oct 23, 2013
Messages
107

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
Joined
Oct 23, 2013
Messages
107
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!
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,639

ADVERTISEMENT

You are welcome.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,711
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Oct 14, 2016
Messages
3,639
...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
Joined
Apr 18, 2011
Messages
36,711
Office Version
  1. 2010
Platform
  1. Windows
@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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

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

Which adblocker are you using?

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
Top