• I'm trying to add up a column of time entries (from various solictors in a legal practice), in the format 911:51 (hours and minutes). Just need to know a formula that will convert the total number of minutes into hours and carry that figure over to add to the other total number of hours, if you see what I mean! I'm Using Excel 97 on a Windows 98 platform.

Johnners

John Marsden

• Welcome to the Forum!

Once you sum up the minutes, divide them as follows
=INT(x/60)
=MOD(x,60)
where x is the total number of minutes.
The first formula will give you the number of hours to add to your hour total and the second formula will give you the minutes left over.

• Welcome to Ozgrid.
There are a different solutions depending on how your data is entered.
Is "911:51" entered as text (with an apostrophe) or is the cell formatted in a time format?

• Note also that you will need to apply a custom number format to the results cell. the format for elapsed time calculations is:

[h]:mm

...etc

• Maybe I mis-understand your question, but if you simply want to have minutes that sum to more than 60 converted into hours in a SUM, then Excel will do that for you. See the attached.

Per Paddyd, make sure you use the correct time formats.

• Thank you all for your excellent tips on solving my problem. I've attached the spreadsheet I am working on but, despite applying the relevant time formatting to the cells, the total refuses to sum up - typing them in manually from scratch works though (the data was exported from an accounts package so perhaps there's a clue there somewhere..).

Derk, your answer looks great but not too sure how to incorporate both formulas tidily.

Once again, thanks to all you guys for your help, it is much appreciated.

Johnners

John Marsden

• Hi Johnners

Yes, your problem is due to the importing from the account package (they have spaces before and after them), here's what to do.

1. Enter =TRIM(C3)*1 in cell D3
2. Format D3 as a Custom Format [h]:mm
3. Copy D3 down as far as needed
4. Copy these formulas
5. Select Cell A1 and go to Edit&gt;Paste Special
6. Check Values and then OK
7. Do the same again and this time Check Formats and then OK

Now add the times in any cell.

• Thanks Dave, that solved my problem completely. Thanks for your time and help. All the best.

Johnners

John Marsden

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!