3rd friday in month

R

ramot06

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
 
A

arno

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


hmmmm....

is the third friday not the only friday between 15th and 21st?

arno
 
A

arno

is the third friday not the only friday between 15th and 21st?

=IF(AND(DAY(a1)>=15,day(a1)<=21,weekday(a1)=6),"3rd friday","ohh")

daddldo

arno
 
P

Peo Sjoblom

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
 
J

Jason Morin

Based on Arno's response, you could use:

=(DAY(A1)>=15)*(DAY(A1)<=21)*(WEEKDAY(A1)=6)

HTH
Jason
Atlanta, GA
 
R

ramot06

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
 
P

Peo Sjoblom

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
 
A

arno

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
 
J

JE McGimpsey

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

arno

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

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

LOL
arno
 
J

JE McGimpsey

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.



=--AND(WEEKDAY(A1)=6,INT(DAY(A1+6)/7)=3)
Click to expand...

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

LOL[/QUOTE]
 
R

ramot06

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 said:
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.





you are right, this (esp. the "--" part) is a lot easier to understand
and reproduce than


LOL
Click to expand...
[/QUOTE]
 
R

ramot06

this is ok ?

=IF(AND(DAY(A3835)>=15,DAY(A3835)<=21,WEEKDAY(A3835)=6,OR(MONTH(A3835)=3,MONTH(A3835)=6,MONTH(A3835)=9,MONTH(A3835)=12)),1,0)
 
Top