INT(DAY(A1)/7)+1)

Found a bug in that.

8/28/1978 = Mon. The formula returns it as the 5th Mon when it should be the
4th Mon. No 5th Mon in 8/78.

11/21/1951 = Wed. The formula returns it as the 4th Wed when it should be
the 3rd Wed.

2/28/2008 = Thu. The formula returns it as the 5th Thu when it should be the
4th Thu. No 5th Thu in 2/08.

This works but may be overly complicated:

INT((WEEKDAY(A1-DAY(A1)+1-WEEKDAY(A1,2),2)+A1-(A1-DAY(A1)+1))/7)

--
Biff
Microsoft Excel MVP


"Ron Rosenfeld" wrote in message
...
On Sat, 15 Nov 2008 04:16:32 -0800 (PST), Totti
wrote:

Hi everyone,
I have in A1:Ax dates, formatted as "mm.dd.yyy", i need a formula that
will tell me which day is that day, but in the known way like monday,
tuesday,.... i know how to do this, i want to get something like this:
FIRST Monday of December 2008, like getting the date and telling me
which monday of this month is it, or say second saturday, fourth
wednesday, such things i mean.

i know how to get the last part (Monday of December 2008) but i have
no clue on how will i check which one is it? like first second, third?
how can i do this?

Thanks in advance for any help

Something like:

=INDEX({"First","Second","Third","Fourth","Fifth"} ,
INT(DAY(A1)/7)+1)&" "&TEXT(A1,"dddd"&" of "&"mmmm yyyy")

--ron