Hi, I've made a spreadsheet which i use to check multiple staff time cards so that i can visually see that all 24 hours of each day have been covered by my staff. It works great for its purpose but I would like to extend its functionality a bit.
On the sheet named "CopyMe" I have the time listed in half hour increments in cells B3-B50 and again in R3-R50. The top 2 Rows of this sheet have the date and Day. In cells C3-Q50 and S3-AH50 I type in the name of staff for each cell or half hour that they have worked. For example if "Jim" worked from 12pm-8pm on June 1st, I would fill in the name "Jim" in each cell in the Range C27-C42. The worksheet does some useful math for me in the bottom rows.
I was hoping that on "Sheet1" I would be able to use formula to find the first instance of the name "Jim" in each column C-Q and S-AH and list the value from Column B in the corresponding Row(Time). Then I need another formula to find the last consecutive instance of the name "Jim". To make things more difficult, an employee could work more than one shift in a 24 hour day so the formula's would have to account for that somehow. For example the overnight worker starts at 10pm and ends at 8am the next morning. For example, lets say "Jon" works on June 1 from 10pm until June 2nd at 8am and the same shift again 24 hours later. That means if we only found the first instance of the name and the last instance of the name in each column, "Jon" would be credited with a 24 hour shift.
I've filled out my example with the scenario's i've described above in an attempt to make this make sense. On "Sheet1" i've manually filled out my desired results. If anyone can help me accomplish this I would be forever grateful. Thank you excel guru's.
Schedules.xlsx
Bookmarks