Another Friday Question

I feel like a dork for not being able to figure out this day of the week
question with so many examples out there, but hey...

Suppose I input a month and a year, how do I find out what date the 3rd
Friday is?
Example: Input: Cell A1 March and Cell B1 2005. Output: Cell C1 3/18/2005

As a follow on, I'd like to figure out how much time in work hours there are
between now (assuming using the now function) and the end of the 3rd Friday.
Will this exclude holidays?

Thanks in advance...
Jeremy

Hi,

Better to put 3 in A1 (instead of the "March" string) or use a lookup to derive
the month number (or 1-12)

Then, for the 3rd Friday:
=DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,2))

The 3rd Friday of a month is always the Friday before the 22nd of that month.

The general formula to find the Nth DOW of a month is (DOW==> 1 for Sun, 2 for
Mon, ..., 7 for Saturday):

=DATE(YYYY,MM,1+N*7)-WEEKDAY(DATE(YYYY,MM,8-DOW))

Regards,

Daniel M.


"Jeremy" <[email protected]> wrote in message
news:[email protected]...
> I feel like a dork for not being able to figure out this day of the week
> question with so many examples out there, but hey...
>
> Suppose I input a month and a year, how do I find out what date the 3rd
> Friday is?
> Example: Input: Cell A1 March and Cell B1 2005. Output: Cell C1 3/18/2005
>
> As a follow on, I'd like to figure out how much time in work hours there are
> between now (assuming using the now function) and the end of the 3rd Friday.
> Will this exclude holidays?
>
> Thanks in advance...
> Jeremy

And the number of hours would be

=NETWORKDAYS(TODAY(),DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,2)),holidays)*8

where holidays is alist of holidays, assuming 8 hour days, and dates
inclusive.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Daniel.M" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> Better to put 3 in A1 (instead of the "March" string) or use a lookup to
derive
> the month number (or 1-12)
>
> Then, for the 3rd Friday:
> =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,2))
>
> The 3rd Friday of a month is always the Friday before the 22nd of that
month.
>
> The general formula to find the Nth DOW of a month is (DOW==> 1 for Sun, 2
for
> Mon, ..., 7 for Saturday):
>
> =DATE(YYYY,MM,1+N*7)-WEEKDAY(DATE(YYYY,MM,8-DOW))
>
> Regards,
>
> Daniel M.
>
>
> "Jeremy" <[email protected]> wrote in message
> news:[email protected]...
> > I feel like a dork for not being able to figure out this day of the week
> > question with so many examples out there, but hey...
> >
> > Suppose I input a month and a year, how do I find out what date the 3rd
> > Friday is?
> > Example: Input: Cell A1 March and Cell B1 2005. Output: Cell C1
3/18/2005
> >
> > As a follow on, I'd like to figure out how much time in work hours there
are
> > between now (assuming using the now function) and the end of the 3rd
Friday.
> > Will this exclude holidays?
> >
> > Thanks in advance...
> > Jeremy
>
>