Time Duration Cell Format in Excel

[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 :

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]

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

[Maverick340]

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

[Bandu]

Originally Posted by Maverick340

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:

[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 !

[Bandu]

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:



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?

[Maverick340]

Hey , I appreciate all the pain you took for my problem thanks a lot !
However like i found out, hh:mm computes in the 24hr format so it computes only upto 23:59. i changed the format to [h]:mm and its working fine now. OOo is Open Office.org the open source counterpart to Microsoft's Office suite. Thanks a ton again