Days in Text to Dates SPECIAL

[DOUG]

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.

 

[Ron Rosenfeld]

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