Week Ended Friday

[Guest]

Hi

Could anyone help me that I am writing a formula the allow me to bring the
date onto week ended Friday, for example cell A1=3 Sep 07 (Monday) cell B1
(where the formula will be) wil show me a result 7 Sep 07 (Friday) and/or
A1=7 Sep 07 (Monday) cell B1 will also show me the result 7 Sep 07 (Friday).
I use vlookup on it doesn't work especailly the date on Friday. Please help.
Thank you.

 

[Rick Rothstein \(MVP - VB\)]

Give this formula a try...

=A1+6-WEEKDAY(A1)

Rick

 

[Guest]

Thank you so much Rick. It works perfectly

Give this formula a try...

=A1+6-WEEKDAY(A1)

Rick

 

[Ron Rosenfeld]

Give this formula a try...

=A1+6-WEEKDAY(A1)

Rick

I'm not sure if it's what the OP wants, but your formula:

Saturday, Sep 01, 2007 --> Friday, Aug 31, 2007

Whereas

=A1+7-WEEKDAY(A1-6)

Saturday, Sep 01, 2007 --> Friday, Sep 07, 2007



--ron

 

[Rick Rothstein \(MVP - VB\)]

Give this formula a try...

I'm not sure if it's what the OP wants, but your formula:

Saturday, Sep 01, 2007 --> Friday, Aug 31, 2007

Whereas

=A1+7-WEEKDAY(A1-6)

Saturday, Sep 01, 2007 --> Friday, Sep 07, 2007

That's a fair point. My impression when I first read the OP's post was that
his dates where weekdays only (no weekends), but that is only a gut-feeling.

Rick