Contact Forum Editor

Send an email to our Forum Editor:

PLEASE NOTE: Your name is used only to let the Forum Editor know who sent the message. Both your name and email address will not be used for any other purpose.

Beginners' Tech Help

It's free to register, to post a question or to start / join a discussion

View Forum Rules | Contact Forum Editor

 

Excel Formula ~ Please

1 next »

wee eddie

Likes # 0

Posted October 31, 2011 at 12:37PM

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.

Like this post
lotvic

Likes # 0

Posted October 31, 2011 at 7:57PM

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

Like this post
wee eddie

Likes # 0

Posted October 31, 2011 at 8:36PM

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.

Like this post
wee eddie

Likes # 0

Posted October 31, 2011 at 8:38PM

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

Like this post
lotvic

Likes # 0

Posted October 31, 2011 at 8:49PM

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

Like this post
lotvic

Likes # 0

Posted October 31, 2011 at 8:56PM

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.

Like this post
lotvic

Likes # 0

Posted October 31, 2011 at 9:04PM

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.

Like this post
wee eddie

Likes # 0

Posted October 31, 2011 at 9:28PM

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.

Like this post
lotvic

Likes # 0

Posted October 31, 2011 at 10:15PM

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

Like this post
lotvic

Likes # 0

Posted October 31, 2011 at 11:40PM

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

Like this post
wee eddie

Likes # 0

Posted November 1, 2011 at 12:31AM

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.

Like this post

1 next »

Reply to this topic

This thread has been locked.



Check out PC Advisor's other tech forums

Send to a friend

Email this article to a friend or colleague:

PLEASE NOTE: Your name is used only to let the recipient know who sent the story. Both your name and the recipient's name and address will not be used for any other purpose.