software

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

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

nwexcelsolutions.com
quot;Carlaquot; gt; wrote in message
...
gt;I need help writing a formula that will tell me what the dates are for the
gt; Thursdays that fall within a specified time period (i.e.
gt; 04/16/06-05/15/06).
gt; I would like each date to fall in it's own cell, so there will either be 4
gt; dates of 5 dates depending on the specific month long span of time.
gt;
gt; Thank you oh genious ones,
gt; Carla
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

quot;Peo Sjoblomquot; wrote:

gt; Use the formula I gave you the other day
gt;
gt; =DATE(YEAR($A$1),MONTH($A$1),1 7*ROWS($A$1:A1))-WEEKDAY(DATE(YEAR($A$1),MONÂ*TH($A$1),8-5))
gt;
gt;
gt;
gt;
gt; put 04/16/06 in A1
gt;
gt;
gt;
gt; put the formula in A2 and copy down and you'll get the Thursdays
gt;
gt;
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt;
gt; quot;Carlaquot; gt; wrote in message
gt; ...
gt; gt;I need help writing a formula that will tell me what the dates are for the
gt; gt; Thursdays that fall within a specified time period (i.e.
gt; gt; 04/16/06-05/15/06).
gt; gt; I would like each date to fall in it's own cell, so there will either be 4
gt; gt; dates of 5 dates depending on the specific month long span of time.
gt; gt;
gt; gt; Thank you oh genious ones,
gt; gt; Carla
gt;
gt;
gt;


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=quot;quot;,quot;quot;,IF(A3 7gt;A$2,quot;quot;,A3 7))--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=539417OK, 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

nwexcelsolutions.com
quot;Carlaquot; gt; wrote in message
...
gt;I did that, and it did give me Thursday dates. Even though I put 04/16/06,
gt; it still gave me dates prior to that. I'm guessing because it is just
gt; giving
gt; me the Thursdays in April. I really need it to tell me ONLY the dates
gt; that
gt; are Thursdays between (and including) the dates of 04/16/05-05/15/05. Can
gt; that be done?
gt;
gt; Thank you again,
gt; Carla
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; Use the formula I gave you the other day
gt;gt;
gt;gt; =DATE(YEAR($A$1),MONTH($A$1),1 7*ROWS($A$1:A1))-WEEKDAY(DATE(YEAR($A$1),MON*TH($A$1),8-5))
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt; put 04/16/06 in A1
gt;gt;
gt;gt;
gt;gt;
gt;gt; put the formula in A2 and copy down and you'll get the Thursdays
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; nwexcelsolutions.com
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Carlaquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I need help writing a formula that will tell me what the dates are for
gt;gt; gt;the
gt;gt; gt; Thursdays that fall within a specified time period (i.e.
gt;gt; gt; 04/16/06-05/15/06).
gt;gt; gt; I would like each date to fall in it's own cell, so there will either
gt;gt; gt; be 4
gt;gt; gt; dates of 5 dates depending on the specific month long span of time.
gt;gt; gt;
gt;gt; gt; Thank you oh genious ones,
gt;gt; gt; Carla
gt;gt;
gt;gt;
gt;gt;
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!

quot;daddylonglegsquot; wrote:

gt;
gt; You could try something like this
gt;
gt; A1=start date, e.g. 4/16/06
gt; A2 =end date, e.g. 5/15/06
gt; A3
gt; =A1 7-WEEKDAY(A1 2)
gt; A4 and copied down as far as necessary
gt; =IF(A3=quot;quot;,quot;quot;,IF(A3 7gt;A$2,quot;quot;,A3 7))
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=539417
gt;
gt;

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.ukquot;Peo Sjoblomquot; gt; wrote in . ..gt; Use the formula I gave you the other daygt;gt;=DATE(YEAR($A$1),MONTH($A$1),1 7*ROWS($A$1:A1 ))-WEEKDAY(DATE(YEAR($A$1),MON*TH($A$1),8-5))gt;gt;gt;gt;gt; put 04/16/06 in A1gt;gt;gt;gt; put the formula in A2 and copy down and you'll get the Thursdaysgt;gt;gt;gt;gt; --gt;gt; Regards,gt;gt; Peo Sjoblomgt;gt; nwexcelsolutions.comgt;gt;gt;gt; quot;Carlaquot; gt; wrote in ...gt;gt;I need help writing a formula that will tell me what the dates are for thegt;gt; Thursdays that fall within a specified time period (i.e.04/16/06-05/15/06).gt;gt; I would like each date to fall in it's own cell, so there will either be4gt;gt; dates of 5 dates depending on the specific month long span of time.gt;gt;gt;gt; Thank you oh genious ones,gt;gt; Carlagt;gt;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()

--
RegardsSandy
In Perth, the ancient capital of Scotland

with @tiscali.co.ukquot;Sandy,

put May 1st 2006 in A1, do toolsgt;optionsgt;calculation 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 weekdayPeo
quot;Sandy Mannquot; gt; wrote in message
...
gt; Peo,
gt;
gt; =DATE(YEAR($A$1),MONTH($A$1),1 7*ROWS($A$1:A1))-4
gt;
gt; seems to work just as well as
gt;
gt; =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
gt; to January 5 2096 anyway when both formulas fail with #NUM!)May I ask if
gt; you have a particular reason for
gt; usingWEEKDAY(DATE(YEAR($A$1),MON*TH($A$1),8-5))I was thinking about
gt; something like when you advised Dave Peterson the otherday about using
gt; ROWS() rather than ROW()--Regards,SandyIn Perth, the ancient capital of
gt; with
gt; @tiscali.co.ukquot;Peo Sjoblomquot; gt; wrote in
gt; . ..gt; Use the formula I
gt; gave you the other
gt; daygt;gt;=DATE(YEAR($A$1),MONTH($A$1),1 7*ROWS($A$1:A1 ))-WEEKDAY(DATE(YEAR($A$1),MON*TH($A$1),8-5))gt;gt;gt;gt;gt;
gt; put 04/16/06 in A1gt;gt;gt;gt; put the formula in A2 and copy down and you'll get
gt; the Thursdaysgt;gt;gt;gt;gt; --gt;gt; Regards,gt;gt; Peo Sjoblomgt;gt;
gt; nwexcelsolutions.comgt;gt;gt;gt; quot;Carlaquot; gt;
gt; wrote in
gt; ...gt;gt;I need
gt; help writing a formula that will tell me what the dates are for thegt;gt;
gt; Thursdays that fall within a specified time period
gt; (i.e.04/16/06-05/15/06).gt;gt; I would like each date to fall in it's own
gt; cell, so there will either be4gt;gt; dates of 5 dates depending on the
gt; specific month long span of time.gt;gt;gt;gt; Thank you oh genious ones,gt;gt; Carlagt;gt;
gt;
Thank you Peo, I just knew that you had to have a good reason but I could
not see it.

--
Thank youSandy
In Perth, the ancient capital of Scotland

with @tiscali.co.ukquot;Peo Sjoblomquot; gt; wrote in message
...
gt; Sandy,
gt;
gt; put May 1st 2006 in A1, do toolsgt;optionsgt;calculation and select 1904 date
gt; system
gt; compare
gt;
gt; Your version won't work in Excel for Mac or any PC that uses that date
gt; system, your formula will return May 4th 2010 which is a Tuesday and mine
gt; (it's not really mine it was adapted from a formula by Daniel Maher who
gt; IMHO knows date formulas like nobody else) will return May 6th 2010 a
gt; Thursday, which gives that if you send a workbook to somebody using that
gt; date system it will be wrong weekday
gt;
gt;
gt; Peo
gt;
gt;
gt;
gt; quot;Sandy Mannquot; gt; wrote in message
gt; ...
gt;gt; Peo,
gt;gt;
gt;gt; =DATE(YEAR($A$1),MONTH($A$1),1 7*ROWS($A$1:A1))-4
gt;gt;
gt;gt; seems to work just as well as
gt;gt;
gt;gt; =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
gt;gt; to January 5 2096 anyway when both formulas fail with #NUM!)May I ask if
gt;gt; you have a particular reason for
gt;gt; usingWEEKDAY(DATE(YEAR($A$1),MON*TH($A$1),8-5))I was thinking about
gt;gt; something like when you advised Dave Peterson the otherday about using
gt;gt; ROWS() rather than ROW()--Regards,SandyIn Perth, the ancient capital of
gt;gt; with
gt;gt; @tiscali.co.ukquot;Peo Sjoblomquot; gt; wrote in
gt;gt; . ..gt; Use the formula I
gt;gt; gave you the other
gt;gt; daygt;gt;=DATE(YEAR($A$1),MONTH($A$1),1 7*ROWS($A$1:A1 ))-WEEKDAY(DATE(YEAR($A$1),MON*TH($A$1),8-5))gt;gt;gt;gt;gt;
gt;gt; put 04/16/06 in A1gt;gt;gt;gt; put the formula in A2 and copy down and you'll get
gt;gt; the Thursdaysgt;gt;gt;gt;gt; --gt;gt; Regards,gt;gt; Peo Sjoblomgt;gt;
gt;gt; nwexcelsolutions.comgt;gt;gt;gt; quot;Carlaquot; gt;
gt;gt; wrote in
gt;gt; ...gt;gt;I need
gt;gt; help writing a formula that will tell me what the dates are for thegt;gt;
gt;gt; Thursdays that fall within a specified time period
gt;gt; (i.e.04/16/06-05/15/06).gt;gt; I would like each date to fall in it's own
gt;gt; cell, so there will either be4gt;gt; dates of 5 dates depending on the
gt;gt; specific month long span of time.gt;gt;gt;gt; Thank you oh genious ones,gt;gt;
gt;gt; Carlagt;gt;
gt;gt;
gt;
gt;