Converting text in a schedule to a date

[GuF021]

Hi,

I am trying to add a worksheet (WO Dec) to an existing monthly work sheet (Dec) work schedule (work book attached) that shows employee availability to cover sick and vacation. I used conditional formatting and text to identify the employees that are currently available and are on a work over list. The work over list gets sorted to identify the date of the last work over. The employee with the longest time since work over gets the first opportunity for open shifts.

My problem is finding the last day an employee on the work over list did a work over, once I have that date, it can be sorted to identify who to call first. Each workday is coded with a text to indicate the type of work ex... D (Day assigned shift), WN (work over night), S (Sick), etc… I need to find the date of the last “WN” or “WD”.

On my test sheet I have been using the Dec calendar and trying to use Nov to identify the last work over. I have tried different reference and logic functions, which I have highlighted yellow in the attached workbook. Some functions work to a point such as MATCH, will identify the nth occurrence, but I can’t get it to work reliably. My thought was to identify the last occurrence then return the date in ROW 1 of the same column, return the formatted date in the cell containing the text. Eventually, if it is possible, I would like to start from yesterday and go back three months.

I am working with Excel 2010, but my company uses 2003, so any solution would need to work on that version. Oh, maybe I should have started with, “I am a novice”. Any help, including your opinion on if this can be done in Excel is appreciated.

Thanks, GuF