Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which day
Hi,
I need to write something which determines whether or not the day is the first, second, third or fourth day. For example if it is Thursday, determine whether or now its the first, second, third or fourth Thursday of the month. Thanks in advance, geebee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which day
Hi,
Alt+F11 to open Vb editor , right click 'ThisWorkbook' and insert module and paste the code in. Call with =DayOfMonth() At present it only works on today date but it would be easy to modify to pass a date parameter. Function DayOfMonth() As String myday = Weekday(Now, 1) For x = 1 To Day(DateSerial(Year(Date), Month(Date) + 1, 1) - 1) testdate = DateSerial(Year(Now), Month(Now), x) testday = Weekday(DateSerial(Year(Now), Month(Now), x)) If testdate Now Then GoTo getmeout If myday = testday Then dom = dom + 1 Next getmeout: DayOfMonth = Date & " is the " & dom & " " _ & WeekdayName(Weekday(Now, 2), abbreviate, 2) _ & " of " & MonthName(Month(Date)) End Function Mike "geebee" wrote: Hi, I need to write something which determines whether or not the day is the first, second, third or fourth day. For example if it is Thursday, determine whether or now its the first, second, third or fourth Thursday of the month. Thanks in advance, geebee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which day
=INT((DAY(A1)-1)/7)+1
But I fear that's too obvious and I'm missing something! Regards, Peter T "geebee" (noSPAMs) wrote in message ... Hi, I need to write something which determines whether or not the day is the first, second, third or fourth day. For example if it is Thursday, determine whether or now its the first, second, third or fourth Thursday of the month. Thanks in advance, geebee |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which day
Looks good and if you really want to get fancy 4thThursday of March =INT((DAY(G10)-1)/7)+1& CHOOSE(INT((DAY(G10)-1)/7)+1,"st","nd","rd","th")& TEXT(G10,"dddd")& " of "& TEXT(G10,"mmmm") -- Don Guillett Microsoft MVP Excel SalesAid Software "Peter T" <peter_t@discussions wrote in message ... =INT((DAY(A1)-1)/7)+1 But I fear that's too obvious and I'm missing something! Regards, Peter T "geebee" (noSPAMs) wrote in message ... Hi, I need to write something which determines whether or not the day is the first, second, third or fourth day. For example if it is Thursday, determine whether or now its the first, second, third or fourth Thursday of the month. Thanks in advance, geebee |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Which day
Add one more "th" to your CHOOSE function to handle the 5th such and such a
day in the month; for example September 30, 2010 will be the 5th Thursday in the month. =INT((DAY(G10)-1)/7)+1& CHOOSE(INT((DAY(G10)-1)/7)+1, "st","nd","rd","th","th")&TEXT(G10,"dddd")&" of "& TEXT(G10,"mmmm") -- Rick (MVP - Excel) "Don Guillett" wrote in message ... Looks good and if you really want to get fancy 4thThursday of March =INT((DAY(G10)-1)/7)+1& CHOOSE(INT((DAY(G10)-1)/7)+1,"st","nd","rd","th")& TEXT(G10,"dddd")& " of "& TEXT(G10,"mmmm") -- Don Guillett Microsoft MVP Excel SalesAid Software "Peter T" <peter_t@discussions wrote in message ... =INT((DAY(A1)-1)/7)+1 But I fear that's too obvious and I'm missing something! Regards, Peter T "geebee" (noSPAMs) wrote in message ... Hi, I need to write something which determines whether or not the day is the first, second, third or fourth day. For example if it is Thursday, determine whether or now its the first, second, third or fourth Thursday of the month. Thanks in advance, geebee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|