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

Humberto Perez

unread,

Dec 10, 2003, 10:56:55 AM12/10/03

to

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
hum....@gte.net

Daniel.M

unread,

Dec 10, 2003, 11:08:45 AM12/10/03

to

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" <hum....@gte.net> wrote in message
news:eNJm8Yzv...@TK2MSFTNGP11.phx.gbl...

Humberto Perez

unread,

Dec 10, 2003, 11:53:25 AM12/10/03

to

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

"Daniel.M" <prenom...@bigfoot.inutil.com> wrote in message
news:OTq0ubzv...@TK2MSFTNGP09.phx.gbl...

Ron Rosenfeld

unread,

Dec 10, 2003, 12:05:03 PM12/10/03

to

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

unread,

Dec 10, 2003, 12:46:40 PM12/10/03

to

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

unread,

Dec 10, 2003, 12:47:12 PM12/10/03

to

> 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

unread,

Dec 10, 2003, 1:50:37 PM12/10/03

to

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" <prenom...@bigfoot.inutil.com> wrote in message

news:%23stOvS0...@TK2MSFTNGP10.phx.gbl...

Daniel.M

unread,

Dec 10, 2003, 4:04:45 PM12/10/03

to

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.

"Humberto Perez" <hum....@gte.net> wrote in message

news:enE4C60v...@tk2msftngp13.phx.gbl...

Ron Rosenfeld

unread,

Dec 10, 2003, 9:09:52 PM12/10/03

to

I like that, too.

--ron

Frank Kabel

unread,

Feb 13, 2004, 6:01:40 PM2/13/04

to

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

Chris wrote:
> 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?

Bill P. Sullivan

unread,

Feb 18, 2004, 3:50:39 PM2/18/04

to

"Chris" <anon...@discussions.microsoft.com> wrote in message
news:3019975C-4E5F-4D90...@microsoft.com...

> 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.