Is there a way to find the 1st Friday of each Month?

  • Thread starter Thread starter Humberto Perez
  • Start date Start date
H

Humberto Perez

I need to track shipments per week and want to have a column heading that
gives me the 1st friday of the month, to calculate the following friday by
adding 7 days. Is there a formula o function that allows me to do that?
thanks
Humberto Perez
(e-mail address removed)
 
Yes.

If you have your year (2003) in A1 and your month number (12) in A2:

First Friday:
=DATE(A1,A2,8)-WEEKDAY(DATE(A1,A2,2))

Saludos,

Daniel M.
 
Gracias Daniel,
It did work. Can you tell me what the 8 and the 2 in the formula means?
Humberto
 
I need to track shipments per week and want to have a column heading that
gives me the 1st friday of the month, to calculate the following friday by
adding 7 days. Is there a formula o function that allows me to do that?
thanks
Humberto Perez
(e-mail address removed)
Click to expand...

If you have a date in the month in A1, then the first Friday is given by the
formula:

=DATE(YEAR(A1),MONTH(A1),1)+
MOD(13-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)


--ron
 
Humberto,
Gracias Daniel,
It did work. Can you tell me what the 8 and the 2 in the formula means?
Click to expand...

Well it means the 8th and the 2nd day of the month.

Basically, you can think of your question in the following way, the first Friday
of the month is the previous Friday of the 8th day of that month.

The formula for previous Friday of a Day D is
=D-Weekday(D-6)

Previous Friday of 8th of the month is:
=D8th - Weekday(D8th - 6)
=D8th - Weekday(D2nd)

Regards,

Daniel M.
 
If you have a date in the month in A1, then the first Friday is given by the
formula:

=DATE(YEAR(A1),MONTH(A1),1)+
MOD(13-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)
Click to expand...

Or (with the same idea as above)

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

Regards,

Daniel M.
 
Daniel, Ron, thanks again for taking the time

Do you recomend any book to understand functions the way you take advantage
of them?

Humberto
 
For a book:
Microsoft Excel 2002 Formulas (With CD-ROM) by John Walkenbach (Author)

But you should pay close attention to the current newsgroup and its ARCHIVES:
http://groups.google.com/advanced_group_search

Put microsoft.public.excel.worksheet.functions in the Newsgroup category and/or
any other criteria. For example, try it with First Friday Month as keywords
(first field).

Saludos,

Daniel M.
 
RE: How about in Access; Need a way to return the 1st Wednesday of each Month? And to do it automatically in an Updatable Query?
 
Thanks Frank - but I thought I was in the Access. My search criteria included Access. I am still learning the Navigation of this site.. ... but at least I know somebody saw my post!! :) c
 
Chris said:
RE: How about in Access; Need a way to return the 1st Wednesday of each
Click to expand...
Month? And to do it automatically in an Updatable Query?

In Excel you use day name function. Not sure about Access.
 
Back
Top