Excel Formula ~ Please

  wee eddie 12:37 31 Oct 11
Locked

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 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 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 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 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 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 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 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 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 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 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.

Advertisement

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

Should I upgrade to Windows 10? Why upgrade to Windows 10? Is Windows 10 good? The pros and cons of!…

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

How to Draw Realistic Female Superheroes

Best Mac security tips: 11 maximum security tricks to keep your Mac safe