order of days

[Totti]

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

 

[muddan madhu]

try this

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

 

[Ron Rosenfeld]

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

 

[Totti]

Thank you very much both,
Ron yours work great.

 

[Ron Rosenfeld]

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

 

[T. Valko]

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)

 

[OssieMac]

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")

 

[T. Valko]

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.

 

[Ron Rosenfeld]

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

 

[Totti]

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