Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
mikeburg
 
Posts: n/a
Default Dates of a Day for a month & year cell formulas


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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Peo Sjoblom
 
Posts: n/a
Default Dates of a Day for a month & year cell formulas

In A4 put

=DATE($A$1,MATCH(A$3,{"January";"February";"March" ;"April";"May";"June";"Jul
y";"August";"September";"October";"November";"Dece mber"},0),1+1*7)-WEEKDAY(D
ATE($A$1,MATCH(A$3,{"January";"February";"March";" April";"May";"June";"July"
;"August";"September";"October";"November";"Decemb er"},0),8-VLOOKUP($B$1,{"S
unday",1;"Monday",2;"Tuesday",3;"Wednesday",4;"Thu rsday",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" wrote in
message ...

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



  #3   Report Post  
Posted to microsoft.public.excel.misc
mikeburg
 
Posts: n/a
Default Dates of a Day for a month & year cell formulas


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


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=496686

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I create a schedule from a list of dates ? Gavin Morris Charts and Charting in Excel 2 October 28th 10 04:08 PM
Counting dates for a the present month but not future months BrianInCalifornia Excel Worksheet Functions 3 December 7th 05 03:00 AM
Am I able to sort dates by month rather than year in excel? Ruth k. Excel Discussion (Misc queries) 1 November 3rd 05 04:18 PM
Formulas within Cell References jhockstr Excel Discussion (Misc queries) 4 August 13th 05 06:40 AM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 03:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"