Formula or macro to generate a date of last weeks Friday

I need a formula or macro or something that generates a specific date to cell A1 using the TODAY date I would imagine. Im not even sure if it can be done.

I always need to use the last weeks Fridays date. So it would be nice to have excel figuring it out automatically.

So lets say today is Saturday the 30th, it would then generate 29/01/2011 to A1.
If today would be Thursday the 03/02/2011 it would still put 29/01/2011 to A1.
If today would be Friday the 04/02/2011 it would still put 29/01/2011 to A1.
But if today would be Wednesday the 09/02/2011 it would now put 04/02/2011 to A1.

So it would always put last week Fridays date to A1 (even if today is Friday, still it should be last weeks friday)

Would be extremely grateful if someone could help me out.
Cheers
Rain

You could try:

Please Login or Register  to view this content.

Am I wrong or today is 29, and last friday is (yesterday) 28th?

Use this:

=A1-WEEKDAY(A1+2, 2)

where in A1 is TODAY date (or you can put it in formula)

Originally Posted by rain4u

even if today is Friday, still it should be last weeks friday

In respect of the above my suggestion should be disregarded. I confess I didn't read the question thoroughly.