Forum Discussion
IF cell is coloured (any colour) count as 15 minutes and Sum as time durration
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?
4 Replies
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.
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?
- 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?
