Calculating Friday Date
 

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

--
chris4652
-----------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php...fo&userid=3590
View this thread: http://www.excelforum.com/showthread.php?threadid=56909

Calculating Friday Date
 

=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" wrote
in message ...

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


--
chris46521
------------------------------------------------------------------------
chris46521's Profile:
http://www.excelforum.com/member.php...o&userid=35909
View this thread: http://www.excelforum.com/showthread...hreadid=569090

Calculating Friday Date
 

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!


Bob Phillips Wrote:
=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"
wrote
in message
...

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


--
chris46521

------------------------------------------------------------------------
chris46521's Profile:
http://www.excelforum.com/member.php...o&userid=35909
View this thread:
http://www.excelforum.com/showthread...hreadid=569090



--
chris46521
------------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php...o&userid=35909
View this thread: http://www.excelforum.com/showthread...hreadid=569090

Calculating Friday Date
 

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



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


--
chris46521
------------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php...o&userid=35909
View this thread: http://www.excelforum.com/showthread...hreadid=569090

Calculating Friday Date
 

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



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


--
chris46521
------------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php...o&userid=35909
View this thread: http://www.excelforum.com/showthread...hreadid=569090

Calculating Friday Date
 

This is another way:

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

RBS


"chris46521" wrote
in message ...

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


--
chris46521
------------------------------------------------------------------------
chris46521's Profile:
http://www.excelforum.com/member.php...o&userid=35909
View this thread: http://www.excelforum.com/showthread...hreadid=569090

Calculating Friday Date
 

It already does keep a Friday date

--

HTH

Bob Phillips

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

"chris46521" wrote
in message ...

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!


Bob Phillips Wrote:
=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"
wrote
in message
...

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


--
chris46521

------------------------------------------------------------------------

chris46521's Profile:
http://www.excelforum.com/member.php...o&userid=35909
View this thread:
http://www.excelforum.com/showthread...hreadid=569090



--
chris46521
------------------------------------------------------------------------
chris46521's Profile:
http://www.excelforum.com/member.php...o&userid=35909
View this thread: http://www.excelforum.com/showthread...hreadid=569090

Calculating Friday Date
 

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

=A1-WEEKDAY(A1+3,3)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=569090

Calculating Friday Date
 

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



"chris46521" wrote:


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


--
chris46521
------------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php...o&userid=35909
View this thread: http://www.excelforum.com/showthread...hreadid=569090

Calculating Friday Date
 

Thanks so much for your help guys

--
chris4652
-----------------------------------------------------------------------
chris46521's Profile: http://www.excelforum.com/member.php...fo&userid=3590
View this thread: http://www.excelforum.com/showthread.php?threadid=56909

Calculating Friday Date
 

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

RBS

"daddylonglegs"
wrote in message
news:daddylonglegs.2c6o1h_1154976610.3693@excelfor um-nospam.com...

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

=A1-WEEKDAY(A1+3,3)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=569090