How do I give columns a value and calculate the total of the highlighted cells

**kittykatcarly16** · 12-12-2008, 10:12 AM

--------------------------------------------------------------------------------

Hi All,
So my boss just walks in, hands me a weeks rota and asks me to do the following:
Put all the info (Day, Name, start and end times) into a spreadsheet
Show how long they work
Show how many staff are on at peak times
Work out how many man hours we're totalling per day

That's all fine.
So I set out the spreadsheet. Day divided into 30 min periods. Person's name and highlight the row to show the hours they have worked.
Simple.

My question is - can I get excel to total up the highlighted hours? How do I give each column the value of 0.5 hours then get it to add up all teh half hours I've highlighted? Can this even be done?

**Ron Coderre** · 12-12-2008, 10:15 AM

Can you post a sample workbook of the data you're working with
and the values you want to calculate? That would surely help
us understand your situation.

**kittykatcarly16** · 12-12-2008, 11:06 AM

Does that help any?

I want to total all the blue rows for each day...

**Ron Coderre** · 12-12-2008, 12:09 PM

Yikes! Totaling by color is usually more trouble than it's worth.

So...Here's what I'm proposing (see attached workbook)
• Change the last time period to 8:30:00 PM, so it calcs as a Time Value
• In each colored cell, enter an "x" (unless it contains a name)
(If you don't want to "see" the x's, change the font color to match the background)

Now, we can calculate the hours worked based on the earliest and latest
non-blank cells using this formula:

Please Login or Register  to view this content.

Copy that formula down as far as you need

Is that something you can work with?

**JBeaucaire** · 12-12-2008, 01:53 PM

I ran into this once, I just put an actual .5 in each of the needed cells, and formatted the font to match the cell color making the number invisible. Now a simple sum formula adds up the rows or columns, whatever.