Search multiple rows for cell color, return date from same column

Hi there, I'm hoping to get some guidance...

Management at my workplace have a Gantt-style spreadsheet for scheduling production. The top row has dates and the left column lists job numbers. Most job numbers repeat on 3 to 5 rows. Departments are identified by cell color. (The values in the colored cells are units of time.)

Based on a list of job numbers and cell colors, I need formulas that would look up the left-most and right-most values for each cell color (department) from all the rows with that job number, and return the dates from the top row in the same columns as those values.

I have a module for the ColorIndex UDF, but I'm not sure how to use it in combination with the other functions needed. Any advice?

Thanks for the quick reply and the reminder about Calculation!

Actually, I was looking for the dates, not the totals... sorry for the confusion. I need the dates to be job-specific. For example, C35 would return January 6 from C3, because that's when the Machining department would finish for job #456. But C36 would return January 7, because that's when Machining would finish for job #789. Does that help?

That did it! Thank you so much!!!