Another Friday Question

[Jeremy]

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

 

[Daniel.M]

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.

 

[Bob Phillips]

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)