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
You can format the cells to time, then a normal formula (+,-,X) will work out the hours.
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]
quote:
Originally posted by designer
You can format the cells to time, then a normal formula (+,-,X) will work out the hours.
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]
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
:sigh: I suppose people see a female name at the top and don't look at the post!!!
quote:
Originally posted by rachaeljf
:sigh: I suppose people see a female name at the top and don't look at the post!!!
quote:
Originally posted by rachaeljf
:sigh: I suppose people see a female name at the top and don't look at the post!!!
Easy Dang, only kidding! 
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.
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]
Someone say something?
Aggghhhh!
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.
Lol, this thread reminds me of http://www.youtube.com/watch?v=DE3r_CgScms