Hello.

How can I code a cell to return the "First Friday in a given month"?
Please assume that cell A1 contains a date which represents the first
day in the month (i.e. 1/1/05 or 5/1/13).

Thanks.
Michael

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

=A1+6-WEEKDAY(A1)

HTH
Jason
Atlanta, GA

-----Original Message-----
Hello.

How can I code a cell to return the "First Friday in a
given month"?
Please assume that cell A1 contains a date which
represents the first
day in the month (i.e. 1/1/05 or 5/1/13).

Thanks.
Michael

*** Sent via Developersdex http://www.developersdex.com
***
Don't just participate in USENET...get rewarded for it!
.

Michael Siegel wrote

Hello.

How can I code a cell to return the "First Friday in a given month"?
Please assume that cell A1 contains a date which represents the first
day in the month (i.e. 1/1/05 or 5/1/13).

Thanks.
Michael

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Try this in cell you want the Friday date:
=A1-WEEKDAY(A1)+6

--
David

This example assumes that Range("B1") is formatted for dates if not it might
return a serial number.


sub findfriday ()
y = Range("a1").Value
goagain:
dayd = Format(y, "dddd")
If dayd = "Friday" Then Range("b1").Value = y: Exit Sub
y = y + 1
GoTo goagain
End Sub
end sub



"Michael Siegel" wrote:

Hello.

How can I code a cell to return the "First Friday in a given month"?
Please assume that cell A1 contains a date which represents the first
day in the month (i.e. 1/1/05 or 5/1/13).

Thanks.
Michael

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

On Mon, 14 Feb 2005 13:25:45 -0800, "Jason Morin"
wrote:

=A1+6-WEEKDAY(A1)


Hmmm. Did you try 1/1/2005?


--ron

On Mon, 14 Feb 2005 12:49:26 -0800, Michael Siegel
wrote:

Hello.

How can I code a cell to return the "First Friday in a given month"?
Please assume that cell A1 contains a date which represents the first
day in the month (i.e. 1/1/05 or 5/1/13).

Thanks.
Michael

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


If the date is always the first of the month, then:

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

For ANY date in a month in A1, then:

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


--ron

Doh!!! September, too! Ugh!

=A1+IF(MONTH(A1+6-WEEKDAY(A1))=MONTH(A1),6-WEEKDAY(A1))

Thanks Ron.

Jason


-----Original Message-----
On Mon, 14 Feb 2005 13:25:45 -0800, "Jason Morin"
wrote:

=A1+6-WEEKDAY(A1)


Hmmm. Did you try 1/1/2005?


--ron
.

The following picks up the first Friday, no matter what date is in B1
=DATE(YEAR(B1),MONTH(B1),CHOOSE(WEEKDAY(DATE(YEAR( B1),MONTH(B1),4)),2,1,7,6,5,4,3))


Modified from Dave McRitchie's site:
http://www.mvps.org/dmcritchie/excel/datetime.htm

Steve

"ben" wrote in message
...
This example assumes that Range("B1") is formatted for dates if not it
might
return a serial number.


sub findfriday ()
y = Range("a1").Value
goagain:
dayd = Format(y, "dddd")
If dayd = "Friday" Then Range("b1").Value = y: Exit Sub
y = y + 1
GoTo goagain
End Sub
end sub



"Michael Siegel" wrote:

Hello.

How can I code a cell to return the "First Friday in a given month"?
Please assume that cell A1 contains a date which represents the first
day in the month (i.e. 1/1/05 or 5/1/13).

Thanks.
Michael

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Ron,

Thanks! I used the first formula, since the referenced date is always
the first day in the month. It worked. Now I just have to analyze the
formula to figure out WHY.

-Michael

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

On Tue, 15 Feb 2005 05:02:02 -0800, Michael Siegel
wrote:

Ron,

Thanks! I used the first formula, since the referenced date is always
the first day in the month. It worked. Now I just have to analyze the
formula to figure out WHY.

-Michael

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Michael,

It was late when I wrote that, but if the day is always the first, then formula
simplifies even further:

=A1+7-WEEKDAY(A1+1)


--ron