order of days
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 |
order of days
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 |
order of days
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 |
order of days
Thank you very much both,
Ron yours work great. |
order of days
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 |
order of days
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 |
order of days
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 |
order of days
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 |
order of days
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 |
order of days
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 |
All times are GMT +1. The time now is 10:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com