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

[mikeburg]

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

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

"mikeburg" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Sandy Mann]

> "mikeburg" <[email protected]> wrote
> in
> message news:[email protected]...

>> 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" <[email protected]> wrote in message
news:OYJws%[email protected]...
> 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

[email protected]
[email protected] with @tiscali.co.uk


"Bob Phillips" <[email protected]> wrote in message
news:OYJws%[email protected]...
> 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" <[email protected]> wrote
> in
> message news:[email protected]...
>>
>> 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
>>
>
>

[mikeburg]

Thanks so very very much, mikeburg