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()
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
Set rTime = Range("C2")
Set rTime = Range(rTime, rTime.End(xlDown))
For Each rCell In rTime
With rCell
sString = .Text
iHours = Val(Left$(sString, 2))
iMinutes = Val(Mid$(sString, 4, 2))
iSeconds = Val(Right$(sString, 2))
.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
Set rTime = Range("C2")
Set rTime = Range(rTime, rTime.End(xlDown))
For Each rCell In rTime
With rCell
sString = .Text
iHours = DatePart("h", sString)
iMinutes = DatePart("n", sString)
iSeconds = DatePart("s", sString)
.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:
|