Prev: Conditional Formatting based on match to external list
Next: Countif for finding a Time input
From: DOUG on 13 Apr 2010 16:51
Through great effort, I have sorted and averaged the patient demand for
appointments over the last three years. For instance, now I know how many
patients to expect on the "3rd Tuesday in November". Is there a way to tell
MS Excel to present to me the date in FY10 which corresponds to the concept
"3rd Tuesday in November"?
DOUG ECKERT in WICHITA KS
PS, I have resorted to filtering a list of dates and a list of "X DAYS OF
THE YEAR WITH X AMOUNT OF DEMAND ON THIS DATE" and pasting the actual dates
to the corresponding rows where I need them one-by-one. It works, but is
very tedious. I would prefer to let the PC do this for me, if possible.
From: Ron Rosenfeld on 13 Apr 2010 17:09
On Tue, 13 Apr 2010 13:51:01 -0700, DOUG <DOUG(a)discussions.microsoft.com>
wrote:

>Through great effort, I have sorted and averaged the patient demand for
>appointments over the last three years. For instance, now I know how many
>patients to expect on the "3rd Tuesday in November". Is there a way to tell
>MS Excel to present to me the date in FY10 which corresponds to the concept
>"3rd Tuesday in November"?
>DOUG ECKERT in WICHITA KS
>PS, I have resorted to filtering a list of dates and a list of "X DAYS OF
>THE YEAR WITH X AMOUNT OF DEMAND ON THIS DATE" and pasting the actual dates
>to the corresponding rows where I need them one-by-one. It works, but is
>very tedious. I would prefer to let the PC do this for me, if possible.

With some date in the month of interest in A1,

=A1-DAY(A1)+1+WN*7-WEEKDAY(A1-DAY(A1)+8-DOW)

will give the Nth day of the week of that month where
WN = Weeknumber (Nominally 1-4 or 5)
DOW = Day of Week (Sun=1, Mon=2, ...)
--ron
 | 
Pages: 1
Prev: Conditional Formatting based on match to external list
Next: Countif for finding a Time input