Find out first Friday every month

[noiseash]

I have problem with writing the formula that copy the content of one sheet to another first Friday every month, some second Friday every month, third, fourth....etc.

What I want is, eg, in cell "A1" of sheet2
if (today=first Friday, sheet1!A1,"")

i don't know how to let the system judge if today is a first Friday of this month or not. I write a formula like this:

if(weekday(today())=5,sheet1!A1,"")
But this only works for every Friday, not for First/Second...Fridays

Any help will be appreciated. Thank you!

[Rowan]

Try:

=IF(DATE(YEAR(TODAY()),MONTH(TODAY()),8)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),2))=TODAY(),Sheet1!A1,"")

Regards
Rowan

"noiseash" wrote:

>
> I have problem with writing the formula that copy the content of one
> sheet to another first Friday every month, some second Friday every
> month, third, fourth....etc.
>
> What I want is, eg, in cell "A1" of sheet2
> if (today=first Friday, sheet1!A1,"")
>
> i don't know how to let the system judge if today is a first Friday of
> this month or not. I write a formula like this:
>
> if(weekday(today())=5,sheet1!A1,"")
> But this only works for every Friday, not for First/Second...Fridays
>
> Any help will be appreciated. Thank you!
>
>
> --
> noiseash
> ------------------------------------------------------------------------
> noiseash's Profile: http://www.excelforum.com/member.php...o&userid=26629
> View this thread: http://www.excelforum.com/showthread...hreadid=401020
>
>

[Ragdyer]

Try this:

=IF(TODAY()=TODAY()-DAY(TODAY())-WEEKDAY(TODAY()-DAY(TODAY())-2)+11,Sheet1!A
1,"")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"noiseash" <[email protected]> wrote in
message news:[email protected]...
>
> I have problem with writing the formula that copy the content of one
> sheet to another first Friday every month, some second Friday every
> month, third, fourth....etc.
>
> What I want is, eg, in cell "A1" of sheet2
> if (today=first Friday, sheet1!A1,"")
>
> i don't know how to let the system judge if today is a first Friday of
> this month or not. I write a formula like this:
>
> if(weekday(today())=5,sheet1!A1,"")
> But this only works for every Friday, not for First/Second...Fridays
>
> Any help will be appreciated. Thank you!
>
>
> --
> noiseash
> ------------------------------------------------------------------------
> noiseash's Profile:
http://www.excelforum.com/member.php...o&userid=26629
> View this thread: http://www.excelforum.com/showthread...hreadid=401020
>

[Ragdyer]

Sorry ! ! !

Tested this formula and forgot to change the date back before posting.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" <[email protected]> wrote in message
news:[email protected]...
> Try this:
>
>
=IF(TODAY()=TODAY()-DAY(TODAY())-WEEKDAY(TODAY()-DAY(TODAY())-2)+11,Sheet1!A
> 1,"")
>
> --
> HTH,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
> "noiseash" <[email protected]> wrote
in
> message news:[email protected]...
> >
> > I have problem with writing the formula that copy the content of one
> > sheet to another first Friday every month, some second Friday every
> > month, third, fourth....etc.
> >
> > What I want is, eg, in cell "A1" of sheet2
> > if (today=first Friday, sheet1!A1,"")
> >
> > i don't know how to let the system judge if today is a first Friday of
> > this month or not. I write a formula like this:
> >
> > if(weekday(today())=5,sheet1!A1,"")
> > But this only works for every Friday, not for First/Second...Fridays
> >
> > Any help will be appreciated. Thank you!
> >
> >
> > --
> > noiseash
> > ------------------------------------------------------------------------
> > noiseash's Profile:
> http://www.excelforum.com/member.php...o&userid=26629
> > View this thread:
http://www.excelforum.com/showthread...hreadid=401020
> >
>

[Bill Martin -- (Remove NOSPAM from address)]

noiseash wrote:
> I have problem with writing the formula that copy the content of one
> sheet to another first Friday every month, some second Friday every
> month, third, fourth....etc.
>
> What I want is, eg, in cell "A1" of sheet2
> if (today=first Friday, sheet1!A1,"")
>
> i don't know how to let the system judge if today is a first Friday of
> this month or not. I write a formula like this:
>
> if(weekday(today())=5,sheet1!A1,"")
> But this only works for every Friday, not for First/Second...Fridays
>
> Any help will be appreciated. Thank you!
>
>


How about:

[ ] = =IF(AND(WEEKDAY(NOW())=1,DAY(NOW())<8),TRUE,FALSE)

Bill

[noiseash]

get a little bit confused for your replies!

I tried both fomulae but not working.

Did you get the wrong one or something?

Sorry ! ! !

Tested this formula and forgot to change the date back before posting.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" <[email protected]> wrote in message
news:[email protected]...
> Try this:
>
>
=IF(TODAY()=TODAY()-DAY(TODAY())-WEEKDAY(TODAY()-DAY(TODAY())-2)+11,Sheet1!A
> 1,"")
>
> --
> HTH,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
> "noiseash" <[email protected]> wrote
in
> message news:[email protected]...
> >
> > I have problem with writing the formula that copy the content of one
> > sheet to another first Friday every month, some second Friday every
> > month, third, fourth....etc.
> >
> > What I want is, eg, in cell "A1" of sheet2
> > if (today=first Friday, sheet1!A1,"")
> >
> > i don't know how to let the system judge if today is a first Friday of
> > this month or not. I write a formula like this:
> >
> > if(weekday(today())=5,sheet1!A1,"")
> > But this only works for every Friday, not for First/Second...Fridays
> >
> > Any help will be appreciated. Thank you!
> >
> >
> > --
> > noiseash
> > ------------------------------------------------------------------------
> > noiseash's Profile:
> http://www.excelforum.com/member.php...o&userid=26629
> > View this thread:
http://www.excelforum.com/showthread...hreadid=401020
> >
>

[Bill Martin -- (Remove NOSPAM from address)]

Bill Martin -- (Remove NOSPAM from address) wrote:
> noiseash wrote:
>
>> I have problem with writing the formula that copy the content of one
>> sheet to another first Friday every month, some second Friday every
>> month, third, fourth....etc.
>>
>> What I want is, eg, in cell "A1" of sheet2
>> if (today=first Friday, sheet1!A1,"")
>>
>> i don't know how to let the system judge if today is a first Friday of
>> this month or not. I write a formula like this:
>>
>> if(weekday(today())=5,sheet1!A1,"")
>> But this only works for every Friday, not for First/Second...Fridays
>>
>> Any help will be appreciated. Thank you!
>>
>>
>
>
> How about:
>
> [ ] = =IF(AND(WEEKDAY(NOW())=1,DAY(NOW())<8),TRUE,FALSE)
>
> Bill
-----------------

Actually, that checks for the first Sunday. As you've probably already
realized, it should be:

[ ] = IF(AND(WEEKDAY(NOW())=6,DAY(NOW())<8),TRUE,FALSE)

Bill

[Ron Rosenfeld]

On Wed, 31 Aug 2005 21:35:50 -0500, noiseash
<[email protected]> wrote:

>
>I have problem with writing the formula that copy the content of one
>sheet to another first Friday every month, some second Friday every
>month, third, fourth....etc.
>
>What I want is, eg, in cell "A1" of sheet2
>if (today=first Friday, sheet1!A1,"")
>
>i don't know how to let the system judge if today is a first Friday of
>this month or not. I write a formula like this:
>
>if(weekday(today())=5,sheet1!A1,"")
>But this only works for every Friday, not for First/Second...Fridays
>
>Any help will be appreciated. Thank you!

The formula to determine the first Friday of a month, with some date in that
month in A1, is:

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+2)

So for your formula:

=IF(TODAY()=TODAY()-DAY(TODAY())+8-WEEKDAY(TODAY()-DAY(TODAY())+2),SHEET1!A1,"")

or, you could do it this way:

=IF(AND(DAY(TODAY())<=7,WEEKDAY(TODAY())=6),Sheet1!A1,"")


--ron