Finding all Mondays in the year in Microsoft Excel. (formula)?

Question

I need to create a spreadsheet that identifies all Mondays in the year by taking into consideration whether its a leap year, and by finding which day of the year the first Monday is on. Can anyone help? It's quite complex! Thank you

Answer 1

All give you some formulas to calculate dates, and use them according to your needs:



To calculate the first or last Monday of a month you need to calculate first the first and last day of the month. Example::

• Lets assume that on cell A2 there is a date, then

• On cell B2 calculate the FIRST day of the month with this formula:

=DATE(YEAR(A$2),MONTH(1),1)

• On cell B2 calculate the LAST day of the month with this formula:

=DATE(YEAR(A$2), MONTH(A$2)+1, 1)-1



• To Calculate the FIRST Monday of the month

=$B$2 + MOD( WEEKDAY($B$2, 3) +0, 7)

TIP: To calculate the first Tuesday, Wednesday,,,, Sunday add the weekday number that identifies the "name" of day you want to calculate:

0=Monday, 1=Tuesday, 2=Wednesday 3=Thursday, 4=Friday 5=Saturday, 6=Sunday, Example:

FIRST FRIDAY =$B$2+ MOD( WEEKDAY($B$2, 3) + 4, 7)

FIRST SUNDAY =$B$2+ MOD( WEEKDAY($B$2, 3) + 6, 7)



• Calculate the LAST Monday of the month

=$C$2 - MOD( WEEKDAY($C$2,3) - 0, 7)

LAST FRIDAY =$C$2 - MOD( WEEKDAY($C$2,3) - 4, 7)

LAST SUNDAY =$C$2 - MOD( WEEKDAY($C$2,3) - 6, 7)



Tip: To calculate all Mondays for a month, first find the first/last Monday and then just add 7 to the previous Monday until the date is greater than the last Monday



I hope this can help you

Answer 2

in case you're taking the 1st Monday of 2011 (01/3/2011) and put in A1 then in A2 form a million/10/2011 decide on the two cells, flow your cursor to the backside top corner of A2 till this is going to become a plus sign, click and drag down till you have the full 12 months (fifty two rows). Excel will proceed the trend.

Answer 3

typically in cell A1 insert the date you wish to start from eg 11/11/10

Then drag this date down 7 rows to A7

in column B1 insert formula =WEEKDAY(A1,2) this fomrula calculates the weekday starting on Monday therefor Mondays will equal 1 - drag down B1 to B7

(=WEEKDAY(A1,3) means monday =0 use whichever you like)

If you want all dates shown then drag from A7 & B7 as far as you need

or

Once you have found first Monday in A1-A7 clear from below the first Monday date (say A3 ) then insert formula =A3+7 into A4 and drag this down as far as required. You can also drag B3 down to check should always = 1



HTH

Answer 4

A1 enter 1/1/2010

A2 =A1+7 copy down to row 53

Adjust A1 to obtain the first Monday

Format the column as dddd dd/mm/yyyy