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

[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)

 

[Daniel.M]

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.

 

[Humberto Perez]

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

 

[Ron Rosenfeld]

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)

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

 

[Daniel.M]

Humberto,

Gracias Daniel,
It did work. Can you tell me what the 8 and the 2 in the formula means?

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.

 

[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)

Or (with the same idea as above)

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

Regards,

Daniel M.

 

[Humberto Perez]

Daniel, Ron, thanks again for taking the time

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

Humberto

 

[Daniel.M]

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.

 

[Ron Rosenfeld]

Or (with the same idea as above)

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

Regards,

Daniel M.

I like that, too.



--ron

 

[Chris]

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?

 

[Frank Kabel]

Hi chris
you should ask MS Access related questions in the Access newsgroup
Frank

 

[Chris]

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

 

[Bill P. Sullivan]

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?

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