| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If weekend date display previous Friday date | Excel Discussion (Misc queries) | |||
| Calculating and listing only Monday-Friday in a work schedule | Excel Discussion (Misc queries) | |||
| friday date | Excel Discussion (Misc queries) | |||
| How can I get a row to display the date of the last friday, and every friday before that? | Excel Programming | |||
| the date always friday | New Users to Excel | |||
![chris46521[_19_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
