How many mondays in a month ?

[gaftalik]

How to find out how many mondays in a given month lets say January 2004 ?

Thank you .

 

[Aladin Akyurek]

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&EOMONTH(A1,0))))=2))

 

[TryingToLearn]

is there a way to accomlish this for 97 which doesn't have the
EOMONTH function?

 

[rrdonutz]

Also:

=INT((EOMONTH(A1,0)-2)/7)-INT((A1-2)/7)+INT(WEEKDAY(A1-2)/7)

where A1 is a 'first of the month' date. This formula is less volatile.

 

[PaddyD]

eomonth() is from the analysis tool pack - will be available in 97 if you activate the tool pack addin

 

[rrdonutz]

One substitute for EOMONTH() is:

DATE(YEAR(A1),MONTH(A1)+1,0)

However, you should have EOMONTH() available to you with Excel 97 through the Analysis ToolPak add-in, even with this version of Excel.

 

[TryingToLearn]

ty everyone!

 

[gaftalik]

My goodness! i like those formulas despite the time they take to analyse them

Thanks to all of you !

 

[AceMangante]

Thank you for this site that i was able to solve my excel problem in getting how many mondays are there in a month. But after knowing it how I found out that i alsol need to get the exact date of all mondays in a month. Say for Jan 2011 there are 5 mondays then i need to know what dates are those. I'm expecting that excel will give me result of Jan 3, Jan 10, Jan 17, Jan 24 and Jan 31. Can you help me please?

 

[Scott R]

=A1+MOD(B1-WEEKDAY(A1),7)
gets you the 1st Monday in the month, with the 1st day of the month in A1 and 2 (or whatever weekday # you want) in B1. Add 7 to this date for subsequent weekdays.