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

try this

=INT(DAY(A1)/7)+1&" "&TEXT(A1,"dddd"&" of "&"mmmm yyyy")



On Nov 15, 5:16*pm, 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

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

Thank you very much both,
Ron yours work great.

On Sat, 15 Nov 2008 06:18:50 -0800 (PST), Totti
wrote:

Thank you very much both,
Ron yours work great.

You're welcome. I see now that mine is pretty much the same as muddan, except
I added a "translation" from "1" to "First" and so forth.
--ron

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

The above formulas are not correct for all occurrences. They do not work
where the particular days are multiples of 7. Examples are Mondays in
January, April and July 2008 where Mondays are 7, 14, 21 and 28.

However, replacing the Int function +1 with Roundup appears to solve the
problem.

=ROUNDUP(DAY(A1)/7,0)&" "&TEXT(A1,"dddd"&" of "&"mmmm yyyy")



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

--
Regards,

OssieMac


"Ron Rosenfeld" wrote:

On Sat, 15 Nov 2008 06:18:50 -0800 (PST), Totti
wrote:

Thank you very much both,
Ron yours work great.

You're welcome. I see now that mine is pretty much the same as muddan, except
I added a "translation" from "1" to "First" and so forth.
--ron

This works but may be overly complicated:
INT((WEEKDAY(A1-DAY(A1)+1-WEEKDAY(A1,2),2)+A1-(A1-DAY(A1)+1))/7)

Found a bug in that one as well. Doesn't work if the dates are in the month
of January in the year 1900.

Looks like OssieMac has a nice compact solution.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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

On Sat, 15 Nov 2008 15:58:01 -0800, OssieMac
wrote:

The above formulas are not correct for all occurrences. They do not work
where the particular days are multiples of 7. Examples are Mondays in
January, April and July 2008 where Mondays are 7, 14, 21 and 28.

However, replacing the Int function +1 with Roundup appears to solve the
problem.

=ROUNDUP(DAY(A1)/7,0)&" "&TEXT(A1,"dddd"&" of "&"mmmm yyyy")



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

Good pickup.
--ron

Indeed you ve been all helpful, Thank you all
But it really "Looks like OssieMac has a nice compact solution" and it
is actually the one that got them all correct. cheers Ossie