Home |
Search |
Today's Posts |
#1
|
|||
|
|||
3rd friday in month
Hi -
I have a cloumn with dates. what i need is that the function will plot 1 if the certain date is the third friday in the relative month and 0 if not. How can I do that ? I searched the net - found close subjects but not exactly this. Thanks |
#2
|
|||
|
|||
Hi
=IF(AND(WEEKDAY(D5,2)=5,DAY(D5)14,DAY(D5)<=21),TR UE,"") Date in D5 This example will return True Tip : See this Add-in if you want to insert this formula in your data table http://www.rondebruin.nl/datarefiner.htm -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... Hi - I have a cloumn with dates. what i need is that the function will plot 1 if the certain date is the third friday in the relative month and 0 if not. How can I do that ? I searched the net - found close subjects but not exactly this. Thanks |
#3
|
|||
|
|||
Hi,
I have a cloumn with dates. what i need is that the function will plot 1 if the certain date is the third friday in the relative month and 0 if not. hmmmm.... is the third friday not the only friday between 15th and 21st? arno |
#4
|
|||
|
|||
is the third friday not the only friday between 15th and 21st?
=IF(AND(DAY(a1)=15,day(a1)<=21,weekday(a1)=6),"3r d friday","ohh") daddldo arno |
#5
|
|||
|
|||
This clever Daniel M formula will always return the date (based on another
date) the third Friday, this assumes the date is in A1, adapt accordingly =DATE(YEAR(A1),MONTH(A1),1+7*3)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-6)) so if you have dates in A1:A50 and want to check them, this formula will return TRUE if third Friday =A1=DATE(YEAR(A1),MONTH(A1),1+7*3)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),8-6)) that way you can copy the formula and it will return TRUE or FALSE Regards, Peo Sjoblom " wrote: Hi - I have a cloumn with dates. what i need is that the function will plot 1 if the certain date is the third friday in the relative month and 0 if not. How can I do that ? I searched the net - found close subjects but not exactly this. Thanks |
#6
|
|||
|
|||
Based on Arno's response, you could use:
=(DAY(A1)=15)*(DAY(A1)<=21)*(WEEKDAY(A1)=6) HTH Jason Atlanta, GA "arno" wrote: Hi, I have a cloumn with dates. what i need is that the function will plot 1 if the certain date is the third friday in the relative month and 0 if not. hmmmm.... is the third friday not the only friday between 15th and 21st? arno |
#7
|
|||
|
|||
I assume that if the data starts from A2 - the formula need to be
changed. Not only the cell refference. I tried it with changing to A2 and it gave me wrong result. If i want it have 1 if true or 0 if false , is it possible or just doing it in a different column ? Thanks |
#8
|
|||
|
|||
sorry. it works good. but still what about 1 or 0 ?
|
#9
|
|||
|
|||
You realize that you must have a date in A2, not text representation of a date
=--(A2=DATE(YEAR(A2),MONTH(A2),1+7*3)-WEEKDAY(DATE(YEAR(A2),MONTH(A2),8-6))) the above will work for A2 and return 1 or 0 for TRUE or FALSE Regards, Peo Sjoblom " wrote: I assume that if the data starts from A2 - the formula need to be changed. Not only the cell refference. I tried it with changing to A2 and it gave me wrong result. If i want it have 1 if true or 0 if false , is it possible or just doing it in a different column ? Thanks |
#10
|
|||
|
|||
"arno" wrote...
is the third friday not the only friday between 15th and 21st? =IF(AND(DAY(a1)=15,day(a1)<=21,weekday(a1)=6),"3 rd friday","ohh") Too redundant. AND(WEEKDAY(A1)=6,INT(DAY(A1+6)/7)=3) |
#11
|
|||
|
|||
Too redundant.
but easy to understand and I explained how it works. pls. explain and prove the logic behind your formula and tell us why it cannot deliver 0 or 1 which was requested by ramot. arno |
#12
|
|||
|
|||
The logic of Harlan's formula is trivial to prove:
If A1 has a DAY(A1)<15, then DAY(A1+6)/7 < 3, so not third Friday. If A1 has a DAY(A1)21, then DAY(A1+6)/7 3, so not third Friday. So only dates between the 15th and the 21st will give a TRUE result. As for why it can't deliver 0 or 1, Harlan was only replacing the conditional portion of your formula, which doesn't return 1 or 0. But it's easy to extend it to do so: =--AND(WEEKDAY(A1)=6,INT(DAY(A1+6)/7)=3) In article , "arno" wrote: but easy to understand and I explained how it works. pls. explain and prove the logic behind your formula and tell us why it cannot deliver 0 or 1 which was requested by ramot. |
#13
|
|||
|
|||
If A1 has a DAY(A1)<15, then DAY(A1+6)/7 < 3, so not third Friday.
If A1 has a DAY(A1)21, then DAY(A1+6)/7 3, so not third Friday. So only dates between the 15th and the 21st will give a TRUE result. =--AND(WEEKDAY(A1)=6,INT(DAY(A1+6)/7)=3) you are right, this (esp. the "--" part) is a lot easier to understand and reproduce than is the third friday not the only friday between 15th and 21st? =IF(AND(DAY(a1)=15,day(a1)<=21,weekday(a1)=6),1, 0) LOL arno |
#14
|
|||
|
|||
Neither Harlan nor I said anything about "easier to understand" (though
for most people, "--", while unfamiliar, is not particularly hard to understand - see http://www.mcgimpsey.com/excel/doubleneg.html). Harlan simply said that the extra function call to DAY() is inefficient, which it is. Nobody's saying your formula didn't work, nor that it was somehow wrong (except that, as originally posted, it didn't meet the OP's 1/0 requirement). For new users, it's probably more readable. Harlan's formula though, has the advantage of efficiency, and it's more easily generalized (e.g., choosing the 2nd Friday can be accomplished by a single change from 3 to 2, or the ordinal value could instead be replaced by a cell reference). And for me, at least, it's more obvious what the criterion is (i.e., the *3rd* friday, rather than having to guess from the weekday numbers). However, in most applications, either works fine. De gustibus non disputandum est. In article , "arno" wrote: =--AND(WEEKDAY(A1)=6,INT(DAY(A1+6)/7)=3) you are right, this (esp. the "--" part) is a lot easier to understand and reproduce than is the third friday not the only friday between 15th and 21st? =IF(AND(DAY(a1)=15,day(a1)<=21,weekday(a1)=6),1, 0) LOL |
#15
|
|||
|
|||
Thanks all. May it possible to condition if the third friday occurs on
eithier March or June or September or December than give 1 else 0 ? Thanks In Advance JE McGimpsey wrote: Neither Harlan nor I said anything about "easier to understand" (though for most people, "--", while unfamiliar, is not particularly hard to understand - see http://www.mcgimpsey.com/excel/doubleneg.html). Harlan simply said that the extra function call to DAY() is inefficient, which it is. Nobody's saying your formula didn't work, nor that it was somehow wrong (except that, as originally posted, it didn't meet the OP's 1/0 requirement). For new users, it's probably more readable. Harlan's formula though, has the advantage of efficiency, and it's more easily generalized (e.g., choosing the 2nd Friday can be accomplished by a single change from 3 to 2, or the ordinal value could instead be replaced by a cell reference). And for me, at least, it's more obvious what the criterion is (i.e., the *3rd* friday, rather than having to guess from the weekday numbers). However, in most applications, either works fine. De gustibus non disputandum est. In article , "arno" wrote: =--AND(WEEKDAY(A1)=6,INT(DAY(A1+6)/7)=3) you are right, this (esp. the "--" part) is a lot easier to understand and reproduce than is the third friday not the only friday between 15th and 21st? =IF(AND(DAY(a1)=15,day(a1)<=21,weekday(a1)=6),1, 0) LOL |
#17
|
|||
|
|||
this is ok ?
=IF(AND(DAY(A3835)=15,DAY(A3835)<=21,WEEKDAY(A383 5)=6,OR(MONTH(A3835)=3,MONTH(A3835)=6,MONTH(A3835) =9,MONTH(A3835)=12)),1,0) |
#18
|
|||
|
|||
thank you !
all the best ramot |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Calculations | Excel Worksheet Functions | |||
Add one month to the previuos month | Excel Discussion (Misc queries) | |||
Month Year Date Format | Excel Worksheet Functions | |||
Accounting Month vs. Calendar Month | Excel Worksheet Functions | |||
Finding Friday | Excel Discussion (Misc queries) |