Calculating Friday Date

[chris46521]

I have dates listed in column A indicating the day an action wa
performed. In column B I need to have the date in column A changed to
Friday. If the date in column A is not a Friday, then the date in colum
B needs to fall back to the previous Friday. For example, if the dat
entered in column A is today, Monday August 7, then the date in colum
B that I would be Friday August 4. Can anyone think of code (or
formula) that can determine this and place the value in column B
Currently I use vlookup to go to another sheet where all the possibl
dates are listed with their respective Fridays, but this slows down th
calculation worksheet as the file is quite large. Thanks for any help o
suggestions

 

[Bob Phillips]

=A1-CHOOSE(WEEKDAY(A1),2,3,4,5,6,0,1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[chris46521]

Thanks a lot for your help! The formula works great, but is there a way
to make so that if the date in column A falls on a Friday that it keeps
that same Friday date in column B? Only the dates that occur on other
days need to fall back to the previous Friday. Also the first cell in B
column always returns the #Value error and I can;t figure out why.
Thanks again for your help. I greatly appreciate it!

 

[Ken]

Chris

=IF(WEEKDAY(s)=2,s,s-WEEKDAY(s)+2)

This is a formula I use to start a bunch of charts on a Monday. s is a
named range that is a data. I think for your purposes you could change
the 2's to 6's and the s's to the cell with the date of interest.

Good luck.

Ken
Norfolk, Va

 

[Ken]

Chris

=IF(WEEKDAY(s)=2,s,s-WEEKDAY(s)+2)

This is a formula I use to start a bunch of charts on a Monday. s is a
named range that is a data. I think for your purposes you could change
the 2's to 6's and the s's to the cell with the date of interest.

Good luck.

Ken
Norfolk, Va

 

[RB Smissaert]

This is another way:

=IF(WEEKDAY(A1)<6,A1-(WEEKDAY(A1)+1),A1 -(WEEKDAY(A1)-6))

RBS

 

[Bob Phillips]

It already does keep a Friday date

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[daddylonglegs]

It doesn't need to be so complicated....

=A1-WEEKDAY(A1+3,3)

 

[Guest]

=IF(WEEKDAY(A1)=6,A1,A1-MOD(WEEKDAY(A1),7)-1)

 

[chris46521]

Thanks so much for your help guys

 

[RB Smissaert]

Yes, it looks simpler, but more complex to understand what is going on.

RBS

"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...