Dates of a Day for a month & year cell formulas

[mikeburg]

I've been trying to come up with a cell formula that returns the date
of certain days of a specified month & 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 t
have a separate sheet for weekly, bi-weekly, monthly, & semi-monthly.

Please help. Thanks so much. mikebur

 

[Peo Sjoblom]

In A4 put

=DATE($A$1,MATCH(A$3,{"January";"February";"March";"April";"May";"June";"Jul
y";"August";"September";"October";"November";"December"},0),1+1*7)-WEEKDAY(D
ATE($A$1,MATCH(A$3,{"January";"February";"March";"April";"May";"June";"July"
;"August";"September";"October";"November";"December"},0),8-VLOOKUP($B$1,{"S
unday",1;"Monday",2;"Tuesday",3;"Wednesday",4;"Thursday",5;"Friday",6;"Satur
day",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="Weekly",A4+7,IF($C$1="Bi-Weekly",$A$4+ROUND(ROW(1:1)*3.5,0),IF($C$
1="Monthly",DATE(YEAR(A4),MONTH(A4)+1,DAY(A4)),"")))

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

 

[mikeburg]

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 & semi-monthly. These two really are not
needed.

Thanks a million. mikeburg