Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
mikeburg
 
Posts: n/a
Default Need cell formulas to return the day of every Monday in a month based on year entered


With a year entered in cell J1, I need formulas to create a list of
Mondays (day only) for each month in column B starting at cell 10 (B10)
based on the entry in J1.

Five cells are allocated per month so if there is only 4 Mondays in a
month, it's 5th cell should be blank.

For example if J 1 has 2006,


January B10 = 2
January B11 = 9
January B12 = 16
January B13 = 23
January B14 = 30
February B15 = 6
February B16 = 13
February B17 = 20
February B18 = 27
February B19 = (blank, no more Monday's in month)
March B20 = 6
March B21 = 13
March B22 = 20
March B23 = 27
March B24 = (blank, no more Monday's in month)
etc


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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Need cell formulas to return the day of every Monday in a month based on year entered

B10: =DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1),2)+1+(WEEKDAY(DATE(A1,1,1),1) <2)*7
B11: =IF(YEAR(B10+7)=$A$1,B10+7,"")

copy B11 down

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"mikeburg" wrote in
message ...

With a year entered in cell J1, I need formulas to create a list of
Mondays (day only) for each month in column B starting at cell 10 (B10)
based on the entry in J1.

Five cells are allocated per month so if there is only 4 Mondays in a
month, it's 5th cell should be blank.

For example if J 1 has 2006,


January B10 = 2
January B11 = 9
January B12 = 16
January B13 = 23
January B14 = 30
February B15 = 6
February B16 = 13
February B17 = 20
February B18 = 27
February B19 = (blank, no more Monday's in month)
March B20 = 6
March B21 = 13
March B22 = 20
March B23 = 27
March B24 = (blank, no more Monday's in month)
etc


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:

http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=547251



  #3   Report Post  
Posted to microsoft.public.excel.misc
Sandy Mann
 
Posts: n/a
Default Need cell formulas to return the day of every Monday in a month based on year entered

"mikeburg" wrote
in
message ...


Five cells are allocated per month so if there is only 4 Mondays in a
month, it's 5th cell should be blank.



"Bob Phillips" wrote in message
...
B10:
=DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1),2)+1+(WEEKDAY(DATE(A1,1,1),1) <2)*7
B11: =IF(YEAR(B10+7)=$A$1,B10+7,"")


This doesn't leave the 5th cell blank (at least for me). I would suggest:

Sealing Daddylonglegs formula as a starting place:

B10: =DATE(A1,1,8)-WEEKDAY(DATE(A1,1,6))
B11: =IF(MONTH(B10)<MONTH(B10+7),"",B10+7)
then in
B12: =IF(B11="",B10+7,IF(MONTH(B11)<MONTH(B11+7),"",B1 1+7))

and copy down the column

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"Bob Phillips" wrote in message
...
B10:
=DATE(A1,1,1)-WEEKDAY(DATE(A1,1,1),2)+1+(WEEKDAY(DATE(A1,1,1),1) <2)*7
B11: =IF(YEAR(B10+7)=$A$1,B10+7,"")

copy B11 down

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"mikeburg" wrote
in
message ...

With a year entered in cell J1, I need formulas to create a list of
Mondays (day only) for each month in column B starting at cell 10 (B10)
based on the entry in J1.

Five cells are allocated per month so if there is only 4 Mondays in a
month, it's 5th cell should be blank.

For example if J 1 has 2006,


January B10 = 2
January B11 = 9
January B12 = 16
January B13 = 23
January B14 = 30
February B15 = 6
February B16 = 13
February B17 = 20
February B18 = 27
February B19 = (blank, no more Monday's in month)
March B20 = 6
March B21 = 13
March B22 = 20
March B23 = 27
March B24 = (blank, no more Monday's in month)
etc


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:

http://www.excelforum.com/member.php...o&userid=24581
View this thread:
http://www.excelforum.com/showthread...hreadid=547251





  #4   Report Post  
Posted to microsoft.public.excel.misc
mikeburg
 
Posts: n/a
Default Need cell formulas to return the day of every Monday in a month based on year entered


Thanks so very very much, mikeburg


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

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 can I highlight a cell if it meets year and month criteria Clyde Excel Worksheet Functions 2 May 11th 06 02:24 PM
Finding the date on the 'nth' Monday in this Month in this Year agarwaldvk Excel Worksheet Functions 1 April 17th 06 10:53 PM
Cell color based upon cell value My View Excel Discussion (Misc queries) 11 July 6th 05 03:59 AM
return array result in cell based on comparing dates Ruthki Excel Worksheet Functions 7 June 30th 05 11:41 PM
Returning a Value to a Cell Based on a Range of Uncertain Size amc422 Excel Worksheet Functions 7 November 14th 04 04:03 PM


All times are GMT +1. The time now is 11:53 AM.

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"