Time format display problem - Excel 2010

[NeilF]

I know this problem is occurring because I don't have the correct cell format, but I can't find the one I need...

I have a model which I'm using to calculate elapsed time, in hours, between 2 times. I also have a lunch break, expressed in minutes, which I want to deduct from the answer.

My model has the following columns (with their formats listed):-

A - date
B - start time (format - Custom, type "h:mm AM/PM")
C - finish time (format - Custom, type "h:mm AM/PM")
D - lunch (format - Custom - "hh:mm")
E - total hours to pay (hours) =C2-B2-D2 (format - Custom, type "hh:mm")

My problem is when I come to total column E (Total hours to pay).

I want my total to appear in hours and minutes, and the format for the cell with the total formula is (format - Custom, type "hh:mm") - the same as the rows above the total.

When I have a small number of hours to total, I get the result I want:-

eg. 05:30 + 05:30 + 05:30 = 16:30.

But when I have a larger number, the result seems to be presented in days, despite the formatting being the same:-

eg. 05:25 + 05:40 + 03.30 + 05.15 + 05:25 = 01:15

whereas I want the result to read 25:15


What am I doing wrong...?

Thanks

Neil

 

[Norie]

Try [h]:mm for the format of the cell with the sum.

 

[NeilF]

Yes, that worked Norie. Many thanks.

Out of interest, what does the [h] bit mean, and how is it normally used?

Thanks again.

Neil

 

[Norie]

Neil

Honest answer, I don't know what it's called and I've only every seen it used with time values.

I'm sure it's used elsewhere though.