order of days

  • Thread starter Thread starter Totti
  • Start date Start date
T

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
 
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
Click to expand...

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.
Click to expand...

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)
 
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")
 
This works but may be overly complicated:
INT((WEEKDAY(A1-DAY(A1)+1-WEEKDAY(A1,2),2)+A1-(A1-DAY(A1)+1))/7)
Click to expand...

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.
 
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")
Click to expand...

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
 
Back
Top