Finding the previous Saturday, then Friday date

[Bob]

Formula #1:
Given any date, I'm trying to come up with a formula that will display the
previous Saturday date. For example, if the "input" (or "base") date is
4/11/2008 (Fri), then the formula should display 4/5/2008 (Sat). However, if
the "input" (or "base") date is 4/12/2008 (Sat), as an example, then the
formula should simply display the same date.

Formula #2:
Using the same "input" (or "base") date that was used in Forumla #1, I need
to come up with a second, but mutually exclusive formula that will display
the Friday date that comes right before the Saturday date that was derived in
Forumla #1. So using the same "input" (or "base") date of 4/11/2008 (Fri),
for example, Formula #2 should display 4/4/2008 (Fri).

Any help in coming up with the two aforementioned formulas will be greatly
appreciated.

Thanks,
Bob

 

[Mike H]

Hi,

previous saturday
=A1-MOD(WEEKDAY(A1),7)
friday before that
=A1-MOD(WEEKDAY(A1),7)-1

Mike

 

[Bob]

Mike,
Thanks for the quick response! I really appreciate your help.
Bob

 

[Ron Rosenfeld]

Formula #1:
Given any date, I'm trying to come up with a formula that will display the
previous Saturday date. For example, if the "input" (or "base") date is
4/11/2008 (Fri), then the formula should display 4/5/2008 (Sat). However, if
the "input" (or "base") date is 4/12/2008 (Sat), as an example, then the
formula should simply display the same date.

=A1+1-WEEKDAY(A1+1)


Formula #2:
Using the same "input" (or "base") date that was used in Forumla #1, I need
to come up with a second, but mutually exclusive formula that will display
the Friday date that comes right before the Saturday date that was derived in
Forumla #1. So using the same "input" (or "base") date of 4/11/2008 (Fri),
for example, Formula #2 should display 4/4/2008 (Fri).

=A1-WEEKDAY(A1+1)


--ron