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 dates of certain days of a specified month & year depending if Weekly, Bi-Weekly, Monthly, or Semi-Monthly. For example

Cell A1 = 2006 (or could be 2005, 2007, etc)
Cell B1 = Sunday (or could be Monday, Tuesday, Wednesday, Thursday, etc)
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, & semi-monthly.

Please help. Thanks so much. mikeburg

[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


--

Regards,

Peo Sjoblom

"mikeburg" <[email protected]> wrote in
message news:[email protected]...
>
> I've been trying to come up with a cell formula that returns the dates
> 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 to
> have a separate sheet for weekly, bi-weekly, monthly, & semi-monthly.
>
> Please help. Thanks so much. mikeburg
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=496686
>

[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