Need cell formulas to return the day of every Monday in a month based on year entered


M

mikeburg

With a year entered in cell J1, I need formulas to create a list o
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
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)
et
 
Ad

Advertisements

B

Bob Phillips

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)
 
S

Sandy Mann

mikeburg said:


Bob Phillips said:
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),"",B11+7))

and copy down the column

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top