Archive of Mr Excel Message Board

Back to Dates in Excel archive index
Back to archive home

A date question

Posted by Fred on October 13, 2001 1:17 PM

Im looking for a function, or macro that will, when passed a date will (1) determine the day of the week and (2)will return the number of times that day occured in the month. For example, if you enter 10/15/01 it will return a 3, because 10/15 is the 3rd monday of the month. 10/29 will return a 5 etc.
Thanks for your help

Re: A date question

Posted by Paul on October 13, 2001 1:45 PM

The first part is easy, if your date is in A1 put =A1 in another cell and format as custom dddd. don't know about the other part

Re: A date question

Posted by Fred on October 13, 2001 2:07 PM

Thanks for your reply Paul ... that much I had, its getting the number that I'm looking for.

Re: A date question

Posted by Aladin Akyurek on October 13, 2001 2:20 PM

Fred,

Assuming the date of interest to be in A1.

In B1 enter: =TEXT(A1,"dddd") [as Paul also suggested ]

In C1 enter: =SUMPRODUCT((WEEKDAY(DATE(YEAR(A1),MONTH(A1),ROW(INDIRECT("1:"&DAY(DATE(YEAR(A1),MONTH(A1),DAY(A1)))))))=VLOOKUP(B1,{"Sunday",1;"Monday",2;"Tuesday",3;"Wednesday",4;"Thursday",5;"Friday",6;"Saturday",7},2,0))+0)

Aladin

=========

Aladin

Posted by Fred on October 13, 2001 9:08 PM

Aladin,

Thanks for the formula, it was a big help. Just for your info, when I ran it as you typed, it returned one less than the number I needed; i.e.
10/15 yields 2nd monday, 10/1 yields 0. I added one to your formula and it works great.
Thanks again

Re: A date question

Posted by IML on October 14, 2001 12:38 PM

Not fully tested, but you could also try
=INT(DAY(A1)/7)+1-(MOD(DAY(A1),7)=0)
where your date is in A1
for the occurance of the day in the month as well.

Fred: I'd suggest switching to Ian's shorter formula. (NT)

Posted by Aladin Akyurek on October 14, 2001 12:58 PM

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.