coozer
|
| posted on 25/5/12 at 11:34 AM |
|
|
Excel formula?
I need a formula for adding up hours..
Say I list start and finish times, minus any breaks...
Start 14.00
Finish 01.30
Break -45
What is the formula to give me the correct, 10.45m?? I've tried the time settings in the formet cells box but all it seems to do is 00.00.00 and
the -45 gives me an input error.
Any ideas??
Its for an invoice by the way
1972 V8 Jago
1980 Z750
|
|
|
|
|
designer
|
| posted on 25/5/12 at 11:57 AM |
|
|
You can format the cells to time, then a normal formula (+,-,X) will work out the hours.
|
|
|
rachaeljf
|
| posted on 25/5/12 at 12:05 PM |
|
|
I do something similar to automate timesheets. Convert your hours to decimal hours (e.g. 01.30 = 1.5 hours), do your sums, then convert back. You will
need the INT function and an IF statement to add 24 hours to your finish time if it is less than your start time. If you really want, you can make
your times display a colon separator rather than the decimal point. Hmm, colon separator, sounds nasty.
Start time: =INT(start)+(start-INT(start))/0.6 - edited for typo!
Finish time: =INT(end)+(end-INT(end))/0.6+IF(end<start,24,0)
Sum: =end-start+break/60
Output in decimal hrs: =INT(sum)+(sum-INT(sum))*0.6
or Output in h:mm : =TEXT(sum/24,"h:mm")
Cheers R
[Edited on 25/5/12 by rachaeljf]
[Edited on 25/5/12 by rachaeljf]
[Edited on 25/5/12 by rachaeljf]
[Edited on 25/5/12 by rachaeljf]
|
|
|
daviep
|
| posted on 25/5/12 at 12:06 PM |
|
|
quote:
Originally posted by designer
You can format the cells to time, then a normal formula (+,-,X) will work out the hours.
The problem is trying to do it over midnight - I was feeling quite clever until I plugged coozers actual time in and it threw a hissy fit 
Davie
“A truly great library contains something in it to offend everyone.”
|
|
|
cerbera
|
| posted on 25/5/12 at 01:02 PM |
|
|
For it too work overnight you need to put the start date and time in one cell and the end date and time in another then simply subtract the cells and
subtract the break.
eg
Cell A1 - Start Time
01/01/2012 14:00
Cell B1 - Finish Time
02/01/2012 01:30
Cell C1 - Break
00:45
Cell D1 - Total Hours Worked
=B1-A1-C1
Format Cell D1 to show hh:mm
[Edited on 25/5/12 by cerbera]
|
|
|
Agriv8
|
| posted on 25/5/12 at 01:46 PM |
|
|
yep I agree you are only going to get this to work placing the date at the front but if you are doing theis daily prefill the dates by entering
01/01/2012 00:00 , 02/01/2012 00:00 , 03/01/2012 00:00 ...... in column a and 02/01/2012 00:00 , 03/01/2012 00:00 04/01/2012 00:00 ........ in column
b you then just need to change the 00:00 to your time
PS think it need to be : so it knows its a time
Agriv8
Taller than your average Guy !
Management is like a tree of monkeys. - Those at the top look down and see a tree full of smiling faces. BUT Those at the bottom look up and see a
tree full of a*seholes .............
|
|
|
rachaeljf
|
| posted on 25/5/12 at 02:04 PM |
|
|
:sigh: I suppose people see a female name at the top and don't look at the post!!!
|
|
|
Slimy38
|
| posted on 25/5/12 at 02:08 PM |
|
|
quote:
Originally posted by rachaeljf
:sigh: I suppose people see a female name at the top and don't look at the post!!!
Don't worry about it, there's been occasions where I've posted something then someone else has pretty much posted the same solution.
We all seem to have 'invisible days'... 
|
|
|
Dangle_kt
|
| posted on 25/5/12 at 02:08 PM |
|
|
quote:
Originally posted by rachaeljf
:sigh: I suppose people see a female name at the top and don't look at the post!!!
Eh?
I'm pretty sure no oneeven noticed your name.
|
|
|
rachaeljf
|
| posted on 25/5/12 at 03:03 PM |
|
|
Easy Dang, only kidding! 
|
|
|
Jaybeee
|
| posted on 25/5/12 at 09:18 PM |
|
|
We use Excel for our timesheets at work I can have a look for you on monday if its not too late. I know we have a custom setting in the cell formats
for when the time goes over 24hrs.
Let me know if you want me to have a look.
|
|
|
rachaeljf
|
| posted on 25/5/12 at 09:59 PM |
|
|
Blimey, it really must be invisible day for me!
Above I have actually reproduced the Excel formulae to manipulate Coozer's input exactly as he typed it. It's much easier to enter times
as HH.MM as if they were a decimal number as it just needs one hand on the numeric keyboard (other hand thus remains free for spannering or the
activity of your choice). With the formulae above there's no need for special formats or having to enter the full date and time. If your finish
time is "earlier" than your start time, i.e. you have gone into the next day, the formula adds 24 hours to the finish time.
Coozer - Enter your data off to the side of your printed invoice and use the TEXT(cell/24,"h:mm") function as above to display your
entered times and output as properly formatted "HH:MM" with the colon separator on the printed invoice. The above formulae are what I use
with the various different styles of client timesheets and my invoices.
Edited to disable smilies!
Cheers R
[Edited on 25/5/12 by rachaeljf]
|
|
|
cerbera
|
| posted on 25/5/12 at 11:08 PM |
|
|
Someone say something?
|
|
|
rachaeljf
|
| posted on 25/5/12 at 11:15 PM |
|
|
Aggghhhh!
|
|
|
cerbera
|
| posted on 26/5/12 at 08:48 AM |
|
|
To be fair Rachael I didn't actually understand your solution as I don't know excel that well so only offered a way I knew.
|
|
|
gingerprince
|
| posted on 26/5/12 at 09:24 AM |
|
|
Lol, this thread reminds me of http://www.youtube.com/watch?v=DE3r_CgScms
|
|
|
