CODEWITHSUNDEEP
excelexcel-2010vba
user3086751
user3086751

Reputation: 205

excel not summing time properly

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

Answers (3)

z̫͋
z̫͋

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

Gary's Student
Gary's Student

Reputation: 96791

With your data in A1, in another cell enter:

=HOUR(A1) & "hr" & MINUTE(A1) & "min"

Upvotes: 0

Greg
Greg

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

Related Questions