First Friday in a Month

[Michael Siegel]

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!

 

[Jason Morin]

=A1+6-WEEKDAY(A1)

HTH
Jason
Atlanta, GA

 

[David]

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

 

[ben]

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

 

[Ron Rosenfeld]

=A1+6-WEEKDAY(A1)

Hmmm. Did you try 1/1/2005?


--ron

 

[Ron Rosenfeld]

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

 

[Jason Morin]

Doh!!! September, too! Ugh!

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

Thanks Ron.

Jason

 

[Steve]

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

 

[Michael Siegel]

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!

 

[Ron Rosenfeld]

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