Need array formula to calculate shift dates

Hi All,

I am trying to create a shift roster in Excel 2010. I have a 77 day rotating roster for 11 staff (each with Day 1 of the roster on a different Monday of the 11 weeks). I have the following:

1. A list of the 77 shifts matched up to a Shift Number (1-77)
2. A list of the 11 dates on which each staff members 'Shift 1' starts

I have created a calendar that allows me to select the staff members name, and the year, and it generates the annual dates/days. Ideally I'd like to include some sort of lookup under each date so that it displays the relevant shift based on the staff member and date.

I'm looking for an array formula that will calculate what shift any given date falls on for a staff member, based on the start date I attribute to their first shift.

Any suggestions would be greatly appreciated!

Cheers

Can you send me a copy of your spreadsheet - Im doing some similar work