IF cell is coloured (any colour) count as 15 minutes and Sum as time durration

[Nickp84]

Hello, 

I'm using Excel as a roster planner /timeline of events. Is it possible to get excel to look at a range of preceding cells in one row and count each coloured cell (colours need to be different ideally) as a duration of 15mins then calculate total duration of the shift? 

Ideally reporting start time and end time (which runs across the higher row) ie

don't count columns A+B ever (headers)

Don't count rows 1 -7 2 ever (headers)

if row 8 row c,d,e, etc is a color count it as 15 mins and return result in row 8, Column AD

in AE return time of row 2 of the corresponding  first colored cell that row  

in AF return time of row 2 of the corresponding  last colored cell that row 

End result showing Shift duration, Start time, End Time.

or is this just too tricky?

[#2]

@Nickp84 

Only Macro can help.

[#3]

OK happy to learn and investigate more... can you suggest some term I can google..

I've done a bit with Macros before but I'm v begginer... do you think I'll be on over my head?

[#4]

@Nickp84 

It may be too tricky. My question is "why the colors in the first place? And how do they get into the cells in question?"

The reason for asking: is there another way to start that same logic in action by using letters or something else other than colors? (i.e., it sounds like you're trying to be tricky, or cool, with the colors, and in the process making it more difficult than it needs to be. Certainly, if a macro is needed to resolve this....)

Is it possible for you to upload a sample (after removing any real names) of what it looks like?

[#5]

@Nickp84 

Have to agree with @mathetes . Why try to calculate with colours. It is so much easier with numbers. I guess that someone is actually selecting the cells of the shift and giving it a color. He/she might as well enter the number 1 and press Ctrl-Enter (on a Mac) and enter it in all selected cells simultaneously. If you then conditionally format the entire roster, all cells with "1" can be given a highlight. And the summary you asked for (Start, Finish and Duration) is then fairly easily calculated in a formula. The attached workbook contains a small scale example. Perhaps you will reconsider your initial design/approach.

By the way, I entered some helper rows at the top (hidden), just to help me create the example. If you already have a row with all quarters in a time format, you may ignore these.