software

I've been trying to come up with a cell formula that returns the dates
of certain days of a specified month amp; year depending if Weekly,
Bi-Weekly, Monthly, or Semi-Monthly. For example

Cell A1 = 2006
Cell B1 = Sunday
Cell C1 = Weekly (or could be Bi-weekly, or Monthly, or Semi-monthly)

Cell A3 = January

Cell A4 = 1-01-06
Cell A5 = 1-08-06
Cell A6 = 1-15-06
Cell A7 = 1-22-06
Cell A8 = 1-29-06

Cell B3 = February

Cell B4 = 2-05-06
Cell B5 = 2-12-06
Cell B6 = 2-19-06
Cell B7 = 2-26-06

To prevent the formula from being overly complex, it may be easier to
have a separate sheet for weekly, bi-weekly, monthly, amp; semi-monthly.

Please help. Thanks so much. mikeburg--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: www.excelforum.com/member.php...oamp;userid=24581
View this thread: www.excelforum.com/showthread...hreadid=496686In A4 put

=DATE($A$1,MATCH(A$3,{quot;Januaryquot;;quot;Februaryquot;;quot;Marchquot; ;quot;Aprilquot;;quot;Mayquot;;quot;Junequot;;quot;Jul
yquot;;quot;Augustquot;;quot;Septemberquot;;quot;Octoberquot;;quot;Novemberquot;;quot;Dece mberquot;},0),1 1*7)-WEEKDAY(D
ATE($A$1,MATCH(A$3,{quot;Januaryquot;;quot;Februaryquot;;quot;Marchquot;;quot; Aprilquot;;quot;Mayquot;;quot;Junequot;;quot;Julyquot;
;quot;Augustquot;;quot;Septemberquot;;quot;Octoberquot;;quot;Novemberquot;;quot;Decemb erquot;},0),8-VLOOKUP($B$1,{quot;S
undayquot;,1;quot;Mondayquot;,2;quot;Tuesdayquot;,3;quot;Wednesdayquot;,4;quot;Thu rsdayquot;,5;quot;Fridayquot;,6;quot;Satur
dayquot;,7},2,0)))

in A5 put

=A4 7

This would be for weekly

copy down

copy across the formula from A4 and A5 to B4 and B5 for February
then copy down.

It would be easy to adapt it to monthly but a bit trickier for bi-weekly and
semi-monthly since it depends on what you mean by that, how would you select
the dates for twice a week

This might cover weekly, bi-weekly and monthly but semi-monthly I guess
depends on how you want it, the first day of the month and the 16th?
For bi-weekly I used the day in C1 4 days

=IF($C$1=quot;Weeklyquot;,A4 7,IF($C$1=quot;Bi-Weeklyquot;,$A$4 ROUND(ROW(1:1)*3.5,0),IF($C$
1=quot;Monthlyquot;,DATE(YEAR(A4),MONTH(A4) 1,DAY(A4)),quot;quot;) ))

To make less of a mess I would probably use different sheets for this
thus eliminating all the IF functions--

Regards,

Peo Sjoblom

quot;mikeburgquot; gt; wrote in
message ...
gt;
gt; I've been trying to come up with a cell formula that returns the dates
gt; of certain days of a specified month amp; year depending if Weekly,
gt; Bi-Weekly, Monthly, or Semi-Monthly. For example
gt;
gt; Cell A1 = 2006
gt; Cell B1 = Sunday
gt; Cell C1 = Weekly (or could be Bi-weekly, or Monthly, or Semi-monthly)
gt;
gt; Cell A3 = January
gt;
gt; Cell A4 = 1-01-06
gt; Cell A5 = 1-08-06
gt; Cell A6 = 1-15-06
gt; Cell A7 = 1-22-06
gt; Cell A8 = 1-29-06
gt;
gt; Cell B3 = February
gt;
gt; Cell B4 = 2-05-06
gt; Cell B5 = 2-12-06
gt; Cell B6 = 2-19-06
gt; Cell B7 = 2-26-06
gt;
gt; To prevent the formula from being overly complex, it may be easier to
gt; have a separate sheet for weekly, bi-weekly, monthly, amp; semi-monthly.
gt;
gt; Please help. Thanks so much. mikeburg
gt;
gt;
gt; --
gt; mikeburg
gt; ------------------------------------------------------------------------
gt; mikeburg's Profile:
www.excelforum.com/member.php...oamp;userid=24581
gt; View this thread: www.excelforum.com/showthread...hreadid=496686
gt;

Thanks, this is great.

However, Bi-Weekly here is to mean every other week. Every 14 days.

How would you show the formula?

You are right about monthly amp; semi-monthly. These two really are not
needed.

Thanks a million. mikeburg--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: www.excelforum.com/member.php...oamp;userid=24581
View this thread: www.excelforum.com/showthread...hreadid=496686