Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default 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   Report Post  
Ron de Bruin
 
Posts: n/a
Default

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   Report Post  
arno
 
Posts: n/a
Default

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   Report Post  
arno
 
Posts: n/a
Default

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   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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   Report Post  
Jason Morin
 
Posts: n/a
Default

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   Report Post  
 
Posts: n/a
Default

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   Report Post  
 
Posts: n/a
Default

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

  #9   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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   Report Post  
Harlan Grove
 
Posts: n/a
Default

"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   Report Post  
arno
 
Posts: n/a
Default

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   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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   Report Post  
arno
 
Posts: n/a
Default

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   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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   Report Post  
 
Posts: n/a
Default

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




  #16   Report Post  
JE McGimpsey
 
Posts: n/a
Default

One way:

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

In article .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 ,
"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   Report Post  
 
Posts: n/a
Default

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   Report Post  
 
Posts: n/a
Default

thank you !

all the best
ramot

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date Calculations Bruce Excel Worksheet Functions 11 May 19th 05 01:09 AM
Add one month to the previuos month heater Excel Discussion (Misc queries) 5 February 10th 05 01:33 AM
Month Year Date Format Jamie Excel Worksheet Functions 2 February 7th 05 07:43 PM
Accounting Month vs. Calendar Month JN Excel Worksheet Functions 4 January 31st 05 09:09 PM
Finding Friday Arlen Excel Discussion (Misc queries) 1 January 23rd 05 06:08 AM


All times are GMT +1. The time now is 04:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"