1

I'm getting some data in the format of text(like for example "12:47" that means 12 hours and 47 minutes) from a URL document and calculating some parameters like overtime work, shortages and so on. How can i sum these data(exceeding 24h) as though at last can show the last number in the right format in a cell like for example "62:51" means 62 hours and 51 minutes.

netTime="5:37"+"7:24"+"14:45"+...

Do i have to convert all data to minutes(or a decimal number) and then sum them and then convert back to the format of time?

noting that formatting like [h]:mm is not applicable here because non of these data(except the final answer) are in cells, they store in variables.

Improve this question
2

1 Answer 1

Reset to default
2

If your original data (to be added) do not exceed 24 hrs you can use simpler code:

Sub TotalTime1()
   Dim a As Date, b As Date, c As Date, netTime As String
   a = "5:37"  'netTime="5:37"+"7:24"+"14:45"+...
   b = "7:24"
   c = "14:45"
   netTime = WorksheetFunction.Text(a + b + c, "[hh]:mm")
   MsgBox netTime
   Range("A1").Value = netTime
End Sub

VBA itself cannot convert times longer than 24 hrs to time value, so you should take advantage of the power of Excel by using the Evaluate method, e.g.

Sub TotalTime2()
   Dim a As String, b As String, c As String, netTime
   a = "25:37"  'netTime="25:37"+"7:24"+"14:45"+...
   b = "7:24"
   c = "14:45"
   netTime = Evaluate("--""" & a & """") + _
             Evaluate("--""" & b & """") + _
             Evaluate("--""" & c & """")
   MsgBox netTime   'General format
   Range("A2").Value = netTime
   Range("A2").NumberFormat = "[hh]:mm"
End Sub

If you use constant strings representing time in your code the notation may be a bit shorter:

Sub TotalTime3()
   Dim netTime
   'netTime="25:37"+"7:24"+"14:45"+...
   netTime = [--"25:37"] + [--"7:24"] + [--"14:45"]
   MsgBox netTime   'General format
   Range("A3").Value = netTime
   Range("A3").NumberFormat = "[hh]:mm"
End Sub
Improve this answer
2
  • Im not familiar with this part: "Evaluate("--""" & a & """") +" What is this and How does it work?
    – alireza Seraj
    Commented Mar 12 at 6:52
  • 1
    The Evaluate method allows you to evaluate Excel expressions in VBA. These expressions must be given in text form. An abbreviated variant with constant expressions written directly and surrounded by square brackets is also possible. In Excel, you can convert a time stored as text into a numeric value by double negation, e.g. --"25:30", even if the time is longer than 24 hours. In VBA, this is impossible, so you should use the Evaluate method as an intermediary. Official description of the method is here: learn.microsoft.com/en-us/office/vba/api/…
    – MGonet
    Commented Mar 12 at 9:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .