Calculate total hour

Helo,

On B1, paste this formula

Please Login or Register  to view this content.

Drag it down to B2, then you can add B1 to B2 normally, the result will be 11 (your expected A3)

Note that you will have to format them in hh:mm.

Is the content of A1 the whole "8:00-15:00 *7hours*"?

If you put the times themselves into single cells you can use simple maths, like B2-B1
If you have sentences in cells instead of numbers you'd need a more complicated formula, which could extract the numbers 7 and 4 (I assume?) from the cells. I recommend you put each time into a single cell (that would mean at least four cells to cover your example). Then the spreadsheet is much easier to maintain.

Hi linzheng

You will be better of keeping the times in separate cells,ie
A1: 08:00 B1: 15:00 then C1: =B1-A1

But for your example, try: =(--RIGHT(A1,5))-(--LEFT(A1,4))+(--(RIGHT(A2,5))-(LEFT(A2,5))) Format cells as hh:mm

I see, then you can change the formula into this

Please Login or Register  to view this content.

and paste it on B1. In case the cell is blank, this formula will return 0 (making it sum-able)

If you don't want to see it return as 0, change the 0 at the end of the formula to ""

Please Login or Register  to view this content.

And use SUM to sum them up (SUM function will ignore text)

Try this...

=IF(A1="","",MOD(MID(A1,FIND("-",A1)+1,4)-LEFT(A1,FIND("-",A1)-1),1))

Format as h:mm

Those formulas will fail if the time rolls over past midnight.

23:00-3:00

Tony, you should change your formula to
=IF(A1="","",MOD(MID(A1,FIND("-",A1)+1,5)-LEFT(A1,FIND("-",A1)-1),1

If you enter 8:00-15:30 for example, it will be 7:03 instead of 7:30

And you are right, in case the time rolls overnight, my formula will return the wrong value while yours still return the correct result.

12:00 in time format is actually equal to 0.5 (half a day, 12/24), so if you multiply the results to 24, you can turn them into number, and you can properly sum them over 24 hours (Remember to format them as General / Number)

Same thing happens to 07:30, if you multiply it with 24, it will turn into 7.5 in General / Number format.

Originally Posted by Lemice

Tony, you should change your formula to
=IF(A1="","",MOD(MID(A1,FIND("-",A1)+1,5)-LEFT(A1,FIND("-",A1)-1),1

Yep! I missed that.

Originally Posted by linzheng

Another question is: is it possible to have the sum in the format of 7.5hrs instead of 07:30?

Do you want the "hrs" to be included?

If so try this:

=IF(A1="","",MOD(MID(A1,FIND("-",A1)+1,5)-LEFT(A1,FIND("-",A1)-1),1)*24&"hrs")

Or, for the result to be in decimal format without the "hrs":

=IF(A1="","",MOD(MID(A1,FIND("-",A1)+1,5)-LEFT(A1,FIND("-",A1)-1),1)*24)

Format both as General