Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thursdays dates between 04/16/06 - 05/15/06
I need help writing a formula that will tell me what the dates are for the
Thursdays that fall within a specified time period (i.e. 04/16/06-05/15/06). I would like each date to fall in it's own cell, so there will either be 4 dates of 5 dates depending on the specific month long span of time. Thank you oh genious ones, Carla |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thursdays dates between 04/16/06 - 05/15/06
Use the formula I gave you the other day
=DATE(YEAR($A$1),MONTH($A$1),1+7*ROWS($A$1:A1))-WEEKDAY(DATE(YEAR($A$1),MON*TH($A$1),8-5)) put 04/16/06 in A1 put the formula in A2 and copy down and you'll get the Thursdays -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Carla" wrote in message ... I need help writing a formula that will tell me what the dates are for the Thursdays that fall within a specified time period (i.e. 04/16/06-05/15/06). I would like each date to fall in it's own cell, so there will either be 4 dates of 5 dates depending on the specific month long span of time. Thank you oh genious ones, Carla |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thursdays dates between 04/16/06 - 05/15/06
I did that, and it did give me Thursday dates. Even though I put 04/16/06,
it still gave me dates prior to that. I'm guessing because it is just giving me the Thursdays in April. I really need it to tell me ONLY the dates that are Thursdays between (and including) the dates of 04/16/05-05/15/05. Can that be done? Thank you again, Carla "Peo Sjoblom" wrote: Use the formula I gave you the other day =DATE(YEAR($A$1),MONTH($A$1),1+7*ROWS($A$1:A1))-WEEKDAY(DATE(YEAR($A$1),MONĀ*TH($A$1),8-5)) put 04/16/06 in A1 put the formula in A2 and copy down and you'll get the Thursdays -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Carla" wrote in message ... I need help writing a formula that will tell me what the dates are for the Thursdays that fall within a specified time period (i.e. 04/16/06-05/15/06). I would like each date to fall in it's own cell, so there will either be 4 dates of 5 dates depending on the specific month long span of time. Thank you oh genious ones, Carla |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thursdays dates between 04/16/06 - 05/15/06
You could try something like this A1=start date, e.g. 4/16/06 A2 =end date, e.g. 5/15/06 A3 =A1+7-WEEKDAY(A1+2) A4 and copied down as far as necessary =IF(A3="","",IF(A3+7A$2,"",A3+7)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=539417 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thursdays dates between 04/16/06 - 05/15/06
OK, I remember, in your first post you said something about Thursdays in one
month, well put this in A2 and copy down =$A$1+ROWS($A$1:A1)*7-WEEKDAY($A$1-5) will give you 04/20/06, 04/27/06 and so on -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Carla" wrote in message ... I did that, and it did give me Thursday dates. Even though I put 04/16/06, it still gave me dates prior to that. I'm guessing because it is just giving me the Thursdays in April. I really need it to tell me ONLY the dates that are Thursdays between (and including) the dates of 04/16/05-05/15/05. Can that be done? Thank you again, Carla "Peo Sjoblom" wrote: Use the formula I gave you the other day =DATE(YEAR($A$1),MONTH($A$1),1+7*ROWS($A$1:A1))-WEEKDAY(DATE(YEAR($A$1),MON*TH($A$1),8-5)) put 04/16/06 in A1 put the formula in A2 and copy down and you'll get the Thursdays -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Carla" wrote in message ... I need help writing a formula that will tell me what the dates are for the Thursdays that fall within a specified time period (i.e. 04/16/06-05/15/06). I would like each date to fall in it's own cell, so there will either be 4 dates of 5 dates depending on the specific month long span of time. Thank you oh genious ones, Carla |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thursdays dates between 04/16/06 - 05/15/06
This one worked beautifully for what I need! Thank you!
Thank you also to PEO, I will keep that formula in the event I need it in the future! "daddylonglegs" wrote: You could try something like this A1=start date, e.g. 4/16/06 A2 =end date, e.g. 5/15/06 A3 =A1+7-WEEKDAY(A1+2) A4 and copied down as far as necessary =IF(A3="","",IF(A3+7A$2,"",A3+7)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=539417 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thursdays dates between 04/16/06 - 05/15/06
Well, I don't know what happened to my previous post it seems to have got
all bunched up some how but here is a re-post that is more readable: Peo, =DATE(YEAR($A$1),MONTH($A$1),1+7*ROWS($A$1:A1))-4 seems to work just as well as =DATE(YEAR($A$1),MONTH($A$1),1+7*ROWS($A$1:A1))-WEEKDAY(DATE(YEAR($A$1),MON*TH($A$1),8-5)) (well,up to January 5 2096 anyway when both formulas fail with #NUM!) May I ask if you have a particular reason for usingWEEKDAY(DATE(YEAR($A$1),MON*TH($A$1),8-5))I was thinking about something like when you advised Dave Peterson the otherday about using ROWS() rather than ROW() -- Regards Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk " |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thursdays dates between 04/16/06 - 05/15/06
Sandy,
put May 1st 2006 in A1, do toolsoptionscalculation and select 1904 date system compare Your version won't work in Excel for Mac or any PC that uses that date system, your formula will return May 4th 2010 which is a Tuesday and mine (it's not really mine it was adapted from a formula by Daniel Maher who IMHO knows date formulas like nobody else) will return May 6th 2010 a Thursday, which gives that if you send a workbook to somebody using that date system it will be wrong weekday Peo "Sandy Mann" wrote in message ... Peo, =DATE(YEAR($A$1),MONTH($A$1),1+7*ROWS($A$1:A1))-4 seems to work just as well as =DATE(YEAR($A$1),MONTH($A$1),1+7*ROWS($A$1:A1))-WEEKDAY(DATE(YEAR($A$1),MON*TH($A$1),8-5))(well,up to January 5 2096 anyway when both formulas fail with #NUM!)May I ask if you have a particular reason for usingWEEKDAY(DATE(YEAR($A$1),MON*TH($A$1),8-5))I was thinking about something like when you advised Dave Peterson the otherday about using ROWS() rather than ROW()--Regards,SandyIn Perth, the ancient capital of with @tiscali.co.uk"Peo Sjoblom" wrote in . .. Use the formula I gave you the other day=DATE(YEAR($A$1),MONTH($A$1),1+7*ROWS($A$1:A1 ))-WEEKDAY(DATE(YEAR($A$1),MON*TH($A$1),8-5)) put 04/16/06 in A1 put the formula in A2 and copy down and you'll get the Thursdays -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Carla" wrote in ...I need help writing a formula that will tell me what the dates are for the Thursdays that fall within a specified time period (i.e.04/16/06-05/15/06). I would like each date to fall in it's own cell, so there will either be4 dates of 5 dates depending on the specific month long span of time. Thank you oh genious ones, Carla |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thursdays dates between 04/16/06 - 05/15/06
Thank you Peo, I just knew that you had to have a good reason but I could
not see it. -- Thank you Sandy In Perth, the ancient capital of Scotland with @tiscali.co.uk "Peo Sjoblom" wrote in message ... Sandy, put May 1st 2006 in A1, do toolsoptionscalculation and select 1904 date system compare Your version won't work in Excel for Mac or any PC that uses that date system, your formula will return May 4th 2010 which is a Tuesday and mine (it's not really mine it was adapted from a formula by Daniel Maher who IMHO knows date formulas like nobody else) will return May 6th 2010 a Thursday, which gives that if you send a workbook to somebody using that date system it will be wrong weekday Peo "Sandy Mann" wrote in message ... Peo, =DATE(YEAR($A$1),MONTH($A$1),1+7*ROWS($A$1:A1))-4 seems to work just as well as =DATE(YEAR($A$1),MONTH($A$1),1+7*ROWS($A$1:A1))-WEEKDAY(DATE(YEAR($A$1),MON*TH($A$1),8-5))(well,up to January 5 2096 anyway when both formulas fail with #NUM!)May I ask if you have a particular reason for usingWEEKDAY(DATE(YEAR($A$1),MON*TH($A$1),8-5))I was thinking about something like when you advised Dave Peterson the otherday about using ROWS() rather than ROW()--Regards,SandyIn Perth, the ancient capital of with @tiscali.co.uk"Peo Sjoblom" wrote in . .. Use the formula I gave you the other day=DATE(YEAR($A$1),MONTH($A$1),1+7*ROWS($A$1:A1 ))-WEEKDAY(DATE(YEAR($A$1),MON*TH($A$1),8-5)) put 04/16/06 in A1 put the formula in A2 and copy down and you'll get the Thursdays -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Carla" wrote in ...I need help writing a formula that will tell me what the dates are for the Thursdays that fall within a specified time period (i.e.04/16/06-05/15/06). I would like each date to fall in it's own cell, so there will either be4 dates of 5 dates depending on the specific month long span of time. Thank you oh genious ones, Carla |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to add dates. | Excel Worksheet Functions | |||
Pre-1900 dates | Excel Discussion (Misc queries) | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
PivotTables - Group Dates, excluding dates | Excel Discussion (Misc queries) | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) |