Excel Formula ~ Please

  wee eddie 12:37 PM 31 Oct 11

I am recording Daily Hours worked in the following way: B1-A1=C1

Where A & B are the Start and Finish Times and C is the number of Hours Worked. These are in the Format 00:00:00 which gives a total of Daily Hours Worked in the Format 00:00:00

The problem: How do I add these up, to give a Weekly Total of Hours Worked.

  lotvic 19:57 PM 31 Oct 11

I believe it's custom Format all cells as [h]:mm or if you want the seconds as well then [h]:mm:ss

  wee eddie 20:36 PM 31 Oct 11

Perhaps I should have said.

How do I add up the C Column?

The Formula that I have tried is =sum(C1:C7) but this does not produce the correct figure.

  wee eddie 20:38 PM 31 Oct 11

Maybe I should also have written hh:mm:ss rather that 00:00:00 but I had assumed that that was, sorta, obvious.

  lotvic 20:49 PM 31 Oct 11

No the =sum(C1:C7) is okay, it's the hh should be [h]

wrong Format 00:00:00 which gives a total of Daily Hours Worked in the Format 00:00:00

should be Format [h]:mm which gives a total of Daily Hours Worked in the Format [h]:mm

or if you want the seconds as well [h]:mm:ss

  lotvic 20:56 PM 31 Oct 11

I'm not an expert, it might be okay just to format the final total cell as [h]:mm I tried it like that and it works but I found it easier to format them all. The square brackets are what you want.

  lotvic 21:04 PM 31 Oct 11

It just occurred to me, can I check that you are highlighting the cells and rightclicking and choosing 'Format Cells' and 'Number tab' in order to change cells to 'time' format.

  wee eddie 21:28 PM 31 Oct 11

Start time:~ 16:30:00 e.g. 4.30pm

Finish time:~ 03:25:00, e.g. 3.25am, the following morning

Total Hours:~ 10:55:00

As I type this I have realised that I will have to decimalise the "Hours Worked Column" (C), possibly in an hidden Column, before I can do this calculation.

  lotvic 22:15 PM 31 Oct 11

Oh, B1-A1 goes over the midnight

you might be better to download a template http://office.microsoft.com/en-us/templates/CT010117277.aspx?av=ZXL

  lotvic 23:40 PM 31 Oct 11

I got it, try this it works for me

A1,A2,A3,A4 custom format to dd/mm/yy hh:mm

B1,B2,B3,B4 custom format to dd/mm/yy hh:mm

C1,C2,C3,C4 custom format to hh:mm

(Total in C5) C5 custom format to [h]:mm

  wee eddie 00:31 AM 01 Nov 11

Unfortunately it's only a small part of a, much, much larger, Stats Sheet.

I was trying to get Weekly Totals as Daily Totals are not a reliable comparison, Year on Year that is, and mean little, as there are far too many variables.


This thread is now locked and can not be replied to.

Best Black Friday deals 2015 live blog: Best UK Black Friday deals & best UK Black Friday tech…

1995-2015: How technology has changed the world in 20 years

The best Black Friday Deals for designers and artists – updated

20 Mac Power User Tricks... That You Didn't Know