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.
Thank you in advance for any help you can give.
Johnners
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.
Was this what you wanted? If not, perhaps attaching a sample workbook would clarify your question.
Hi,
here are several sites that have good help on this subject
2rrs
http://www.ozgrid.com/Excel/ExcelDateandTimes.htm
http://www.mvps.org/dmcritchie/excel/datetime.htm
http://www.cpearson.com/excel/overtime.htm
http://j-walk.com/ss/excel/files/timesht.htm
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
Paddy
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
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>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
Don’t have an account yet? Register yourself now and be a part of our community!
