Reputation: 205
I would like to know how I can go about formatting a time so that it shows on the spreadsheet as 10hr12min not as 10:12, preferably as vba. becaue a change could take 24 hours and it come up as 00:00 and when i sum two times like this it should be 48 hours and 0 minutes not just 00:00
Upvotes: 0
Views: 489
Reputation: 1571
As mentioned by Greg, the format to display correctly the sum of time data properly is with [h]
instead of h
, in your case [h]"hr"mm"min"
In VBA:
Range("A1").NumberFormat = "[h]""hr""mm""min"""
Upvotes: 2
Reputation: 96791
With your data in A1, in another cell enter:
=HOUR(A1) & "hr" & MINUTE(A1) & "min"
Upvotes: 0
Reputation: 141
Try custom formatting the cell to: [h]:mm
This will keep just the hours and minutes in the cell instead of the actual time. The sum will then work appropriately.
Credit to: Mr Excel form archives
Upvotes: 1