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
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
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.
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
=========
Posted by Fred on October 13, 2001 9:08 PM
Aladin
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
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.
Posted by Aladin Akyurek on October 14, 2001 12:58 PM
Fred: I'd suggest switching to Ian's shorter formula. (NT)
