# Time Duration Cell Format in Excel

Status
Not open for further replies.

#### Maverick340

I am making a worksheet in which i need to calculate the total hours of duration. I have a cell column which has number of hours in the format of hh:mm.
See the below screenshot of further details :
*img519.imageshack.us/img519/9098/excelprobtd4.png
What i want is the total number of hours/minutes at the end. However the format i have set is not getting accepted. The value gets reverted to AM/PM. Thus i am not getting the proper total..

#### Bandu

##### Journeyman
*i38.tinypic.com/35jkm81.jpg

Edit: There is a mistake in my image. I said A1*1440. It should have been B1*1440

OP

#### Maverick340

But i want it in the hh:mm format . Also is it really that tough to get a time-duration format . Omg !

#### Bandu

##### Journeyman
What i want is the total number of hours/minutes at the end.
I thought that you wanted the total. I failed to understand that you wanted it in hh:mm format.

So, if you just reversed things from the total minutes, won't it give you in hh_mm format. See this pic. I added another column with the opposite formula:

*i34.tinypic.com/2irrhu9.jpg

Last edited:
OP

#### Maverick340

Well i firstly do not want a desperate column. And all the value are in the format of hh:mm
so the product also should be in hh:mm
Whats hh_mm ?

Edit : If OOo has a simpler way, i swear i am switching today !

EDIT EDIT : Found the solution, and yes its is simpler ! simply put the custom format as [h]:mm . the square brackets allows it to compute values more than 24. the hh:mm resets to 0:00 after crossing 23:59. MS you survive today ... bah !

Last edited:

#### Bandu

##### Journeyman
1. It has been a lot confusing. I read that you wanted a sum in hours / minutes. And obviously took it for a numeric value rather than a formatted one. My mistake.

2. The reason I put all those in separate columns was to give a better understanding / view of how looked and how they were worked upon.

3. Ignore the hh_mm. I wanted to say hh:mm. Was a typo.

Now that we are back at the same place where we started... I do not know why your excel complains about invalid format. For me, I just wrote down some figures in hh:mm format and at the end totalled them, My smartass German version of excel gave me the output as the input. No need of any extra effort. As here:

*i38.tinypic.com/x6cspj.jpg

Note that the function Sum() is called Summe() in German (in the above image). It still is the Sum() function.

Now, to solve your problem, I suggest you try the following:
1. Sum the columns. Let it complain.
2. Right click on the cell. Select format. Select User Defined, and then on the right side select hh:mm. That should work.

If it dosen't, then unfortunately, I can't help. Maybe someone with English version of excel can help.

Regards,
Bandu.
Edit: Whats OOo?

OP