Dates returned for previous Sunday

caj1980

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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0
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:
Upvote 0
For previous Sunday: =TODAY()-WEEKDAY(TODAY(),2)
For Sunday before that: =TODAY()-7-WEEKDAY(TODAY(),2)
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
...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:
Upvote 0
@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.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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
Back
Top