RSS

Calculating time with VBA

This page shows examples of how to get hours, minutes and seconds out of a date formatted time like e.g. 03:40:51 (3 hours, 40 minutes and 51 seconds). You can also see how to find the elapsed time between two dates with times.

You can use VBA's string functions Left, Mid and Right (Method 1) or the date function DatePart (Method 2). The date function DateDiff can also find the time difference between two times if they also contain a date (Method 3).

If you want to test the examples, highlight the code with the mouse, copy (CTRL+C) and paste it (CTRL+V) into a VBA module.

The examples come from a task I had at work. A part of the plant had too many unplanned stops, and I wanted to find the total downtime for a period.

I could get the times for stop and restart and typed them manually into two columns like this:

A         B
Stop      Start
13:20:01  17:00:21
05:08:31  05:43:21
06:37:31  07:25:21
23:50:11  03:47:41
osv.

(You may have to format the cells as user defined "hh:mm:ss".)

In Excel you can subtract one time from another, for instance cell B2 - A2 = 03:40:20, but if you do it that way, you get a problem in row 5, where we pass midnight.

So I typed the following formula in C2: "= 24 - A2 + B2" and copied down. Now I got the elapsed time in hours, minutes and seconds expressed as e.g. "03:40:20".

However you cannot add these times to one another. You must extract hours, minutes and seconds before you calculate. That is turn 03:40:20 into "3", "40" and "20".

For this you can use spreadsheet functions, but VBA is ideal for such a job - especially if it is all code based.

Below I show how to extract hours, minutes and seconds by treating the time as a text string (Method 1) or by using the date function DatePart (Method 2).

If there is more than 24 hours between the two times you need the date too, e.g.: "01-12-10 03:35:27" and in that case you must use the date function DateDiff. How to do that is outlined in Method 3.

If you want to test the code, highlight it with the mouse, copy (CTRL+C) and paste (CTRL+V) into a VBA module. If you are viewing this page on a device with a small screen, some of the code lines may appear "broken," but they will be okay, when you paste.

Method 1: Using string functions


Sub CalcTime()
'Reads the date formatted times and extracts hours, minutes and
'seconds so you can find the total.

Dim sString As String
Dim iHours As Integer
Dim iMinutes As Integer
Dim iSeconds As Integer
Dim rTime As Range
Dim rCell As Range

On Error GoTo ErrorHandle

'Find the first cell in the column with date formatted times.
Set rTime = Range("C2")

'Expand the range to the first empty cell.
Set rTime = Range(rTime, rTime.End(xlDown))

'Loop through rTime
For Each rCell In rTime
   With rCell
      'Because the cell contains a formula, we set
      'our string variable = the cell's text property.
      sString = .Text

      'The hours are the first two characters,
      'so we use the Left function.
      'Because "Left$", "Mid$" and "Right$" return
      'the data type "String", we start with a "Val"
      'to convert to a numeric value.
      iHours = Val(Left$(sString, 2))

      'Minutes are the two numbers in position
      '4 and 5, so we use the Mid function.
      iMinutes = Val(Mid$(sString, 4, 2))

      'Seconds are the two last numbers, so we
      'use the Right function.
      iSeconds = Val(Right$(sString, 2))

      'Hours, minutes and seconds are inserted in the
      'columns to the right of the date formatted times.
      .Offset(0, 1).Value = iHours
      .Offset(0, 2).Value = iMinutes
      .Offset(0, 3).Value = iSeconds
   End With
Next

BeforeExit:
Set rCell = Nothing
Set rTime = Nothing
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

Method 2: Using the function DatePart

DatePart is one of VBA's date functions, and it can extract year, date, month, week, hours, minutes and seconds from a date with a time stamp. In the following example I use it on the time stamp only.


Sub CalcTime2()

Dim sString As String
Dim iHours As Integer
Dim iMinutes As Integer
Dim iSeconds As Integer
Dim rTime As Range
Dim rCell As Range

On Error GoTo ErrorHandle

'Finds the first cell in the column with date formatted times.
Set rTime = Range("C2")

'Extends the range to the first empty cell in the column.
Set rTime = Range(rTime, rTime.End(xlDown))

'We loop through the range.
For Each rCell In rTime
   With rCell
      'Because the cell contains a formula, we set
      'the string variable = the cells text property.
      sString = .Text

      'The DatePart function is now used to
      'extract hours, minutes and seconds.
      iHours = DatePart("h", sString)
      iMinutes = DatePart("n", sString)
      iSeconds = DatePart("s", sString)

      'Hours, minutes and seconds are inserted in
      'the columns next to the date formatted times.
      .Offset(0, 1).Value = iHours
      .Offset(0, 2).Value = iMinutes
      .Offset(0, 3).Value = iSeconds
   End With
Next

BeforeExit:
Set rCell = Nothing
Set rTime = Nothing
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

Method 3

If there are more than 24 hours between two time stamps, you can't use the methods above. Then you must use the date function DateDiff, which needs the date too. E.g.: 01-12-10 03:20:43

With two time stamps, e.g. "01-12-10 03:20:43" and "03-12-10 17:01:22", you can get the difference in seconds with VBA code like this:

lSeconds = DateDiff("s",Time1, Time2)

Where lSeconds is a variable of the data type Long, and Time1 and Time2 are of the data type Date. See the VBA help for more information about using the DateDiff function to find the difference in years, months, weeks etc.

If you extract seconds then use the data type long. The numbers grow very big, and you will get an overflow error, if you use Integer (max 32767) and don't use one of the newer Excel versions that automatically converts Integer to Long.

Maybe you can find differences in time without using VBA. I just don't know how, and to be honest I think VBA is more fun!

Related: