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.


hmmmm....

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

arno

thank you !

all the best
ramot

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)

One way:

=--AND(MOD(MONTH(A1),3)=0,WEEKDAY(A1)=6,INT(DAY(A1+6)/7)=3)

In article <1118518438.410022.310280@g44g2000cwa.googlegroups.com>,
ramot06@yahoo.com wrote:

> 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 <usootiPbFHA.1404@TK2MSFTNGP09.phx.gbl>,
> > "arno" <schoblochr@azoppoth.at> 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

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 <usootiPbFHA.1404@TK2MSFTNGP09.phx.gbl>,
> "arno" <schoblochr@azoppoth.at> 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

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 <usootiPbFHA.1404@TK2MSFTNGP09.phx.gbl>,
"arno" <schoblochr@azoppoth.at> 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

> 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

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 <#Ralr4MbFHA.1660@tk2msftngp13.phx.gbl>,
"arno" <schoblochr@azoppoth.at> 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.

> 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

"arno" <schoblochr@azoppoth.at> wrote...
>>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")

Too redundant.

AND(WEEKDAY(A1)=6,INT(DAY(A1+6)/7)=3)

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

"ramot06@yahoo.com" 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
>
>

Hi

=IF(AND(WEEKDAY(D5,2)=5,DAY(D5)>14,DAY(D5)<=21),TRUE,"")

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


<ramot06@yahoo.com> wrote in message news:1118242583.043283.319060@g44g2000cwa.googlegroups.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
>

sorry. it works good. but still what about 1 or 0 ?

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

> 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

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
>

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

"ramot06@yahoo.com" 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
>
>

thank you !

all the best
ramot

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)

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

> 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

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

"ramot06@yahoo.com" 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
>
>

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
>

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

sorry. it works good. but still what about 1 or 0 ?

Hi

=IF(AND(WEEKDAY(D5,2)=5,DAY(D5)>14,DAY(D5)<=21),TRUE,"")

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


<ramot06@yahoo.com> wrote in message news:1118242583.043283.319060@g44g2000cwa.googlegroups.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
>

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

"ramot06@yahoo.com" 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
>
>

"arno" <schoblochr@azoppoth.at> wrote...
>>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")

Too redundant.

AND(WEEKDAY(A1)=6,INT(DAY(A1+6)/7)=3)

> 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

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 <#Ralr4MbFHA.1660@tk2msftngp13.phx.gbl>,
"arno" <schoblochr@azoppoth.at> 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.

> 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

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 <usootiPbFHA.1404@TK2MSFTNGP09.phx.gbl>,
"arno" <schoblochr@azoppoth.at> 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

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 <usootiPbFHA.1404@TK2MSFTNGP09.phx.gbl>,
> "arno" <schoblochr@azoppoth.at> 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

One way:

=--AND(MOD(MONTH(A1),3)=0,WEEKDAY(A1)=6,INT(DAY(A1+6)/7)=3)

In article <1118518438.410022.310280@g44g2000cwa.googlegroups.com>,
ramot06@yahoo.com wrote:

> 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 <usootiPbFHA.1404@TK2MSFTNGP09.phx.gbl>,
> > "arno" <schoblochr@azoppoth.at> 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

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

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 <#Ralr4MbFHA.1660@tk2msftngp13.phx.gbl>,
"arno" <schoblochr@azoppoth.at> 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.

> 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

> 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

"arno" <schoblochr@azoppoth.at> wrote...
>>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")

Too redundant.

AND(WEEKDAY(A1)=6,INT(DAY(A1+6)/7)=3)

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

"ramot06@yahoo.com" 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
>
>

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 <usootiPbFHA.1404@TK2MSFTNGP09.phx.gbl>,
"arno" <schoblochr@azoppoth.at> 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

Hi

=IF(AND(WEEKDAY(D5,2)=5,DAY(D5)>14,DAY(D5)<=21),TRUE,"")

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


<ramot06@yahoo.com> wrote in message news:1118242583.043283.319060@g44g2000cwa.googlegroups.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
>

sorry. it works good. but still what about 1 or 0 ?

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

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 <usootiPbFHA.1404@TK2MSFTNGP09.phx.gbl>,
> "arno" <schoblochr@azoppoth.at> 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

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
>

One way:

=--AND(MOD(MONTH(A1),3)=0,WEEKDAY(A1)=6,INT(DAY(A1+6)/7)=3)

In article <1118518438.410022.310280@g44g2000cwa.googlegroups.com>,
ramot06@yahoo.com wrote:

> 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 <usootiPbFHA.1404@TK2MSFTNGP09.phx.gbl>,
> > "arno" <schoblochr@azoppoth.at> 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

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

"ramot06@yahoo.com" 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
>
>

> 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

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)

thank you !

all the best
ramot

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

"ramot06@yahoo.com" 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
>
>

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

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

> 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

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

"ramot06@yahoo.com" 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
>
>

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
>

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

sorry. it works good. but still what about 1 or 0 ?

Hi

=IF(AND(WEEKDAY(D5,2)=5,DAY(D5)>14,DAY(D5)<=21),TRUE,"")

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


<ramot06@yahoo.com> wrote in message news:1118242583.043283.319060@g44g2000cwa.googlegroups.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
>

thank you !

all the best
ramot

"arno" <schoblochr@azoppoth.at> wrote...
>>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")

Too redundant.

AND(WEEKDAY(A1)=6,INT(DAY(A1+6)/7)=3)

> 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

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 <#Ralr4MbFHA.1660@tk2msftngp13.phx.gbl>,
"arno" <schoblochr@azoppoth.at> 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.

> 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

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 <usootiPbFHA.1404@TK2MSFTNGP09.phx.gbl>,
"arno" <schoblochr@azoppoth.at> 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

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 <usootiPbFHA.1404@TK2MSFTNGP09.phx.gbl>,
> "arno" <schoblochr@azoppoth.at> 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

One way:

=--AND(MOD(MONTH(A1),3)=0,WEEKDAY(A1)=6,INT(DAY(A1+6)/7)=3)

In article <1118518438.410022.310280@g44g2000cwa.googlegroups.com>,
ramot06@yahoo.com wrote:

> 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 <usootiPbFHA.1404@TK2MSFTNGP09.phx.gbl>,
> > "arno" <schoblochr@azoppoth.at> 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

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)