Subtract # of days from date, but if not sat, goto previous sat?

Need to calculate dates by subtracting a certain number of days, but if it is
not a particular day of the week, it needs to go back to the previous week
and give me the date of that particular day of the week.

something like:

=IF(WEEKDAY(TODAY()-B9,1) =
3,TODAY()-B9,TODAY()-B9-(WEEKDAY(TODAY()-B9))-(7-3))

B9 contains the number of days to subtract
Where 3 represents Tuesday. Change to suit.

--
Regards,
Tom Ogilvy


"Fernando" wrote:

> Need to calculate dates by subtracting a certain number of days, but if it is
> not a particular day of the week, it needs to go back to the previous week
> and give me the date of that particular day of the week.

Tom,
The formula works, but in some cases it would go back an extra week. For
example I have to go back 28 days from 10/10/06 and make sure that it is a
Sunday. If you subtract 28 days to Oct 10, you end up at Tue Sept 12th. If
you have to go back to the closest Sunday, then the formula should give you
Sun Sept 10th, but it is giving me Sun Sept 03. The funny thing is that
works for some days, but for other do not work. Can you help me?

Fernando


"Tom Ogilvy" wrote:

> something like:
>
> =IF(WEEKDAY(TODAY()-B9,1) =
> 3,TODAY()-B9,TODAY()-B9-(WEEKDAY(TODAY()-B9))-(7-3))
>
> B9 contains the number of days to subtract
> Where 3 represents Tuesday. Change to suit.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Fernando" wrote:
>
> > Need to calculate dates by subtracting a certain number of days, but if it is
> > not a particular day of the week, it needs to go back to the previous week
> > and give me the date of that particular day of the week.

Hi Fernando, try this formula, again B9 is the number of days to subtract but the 2 represents Tuesday (0=sun through to 6 =sat)

=TODAY()-B9-WEEKDAY(TODAY()-B9-2)+1

so if you always want to find a Sunday it's just

=TODAY()-B9-WEEKDAY(TODAY()-B9)+1