VBA getting the difference between Time Stamps

[jscranton]

I am looping through a column of TimeStamps in this format:

6/7/2019 9:15:19 AM CDT

I want to find the difference between the first and last time in terms of hours and minutes preferably in a format such as 8.5.

in the below code, sRange is an array of data containing the timestamp.

    fAction = 0
    lAction = 0
    For x = LBound(sRange) To UBound(sRange)
            If sRange(x, 5) > fAction Then fAction = sRange(x, 5)
            If sRange(x, 5) < lAction Then lAction = sRange(x, 5)
    Next x

     difference = lAction - fAction

 

[JoeMo]

Assuming all the entries in your data are legitimate dates and all for the Central time zone, here's a UDF then can be used like a worksheet function (as in the example below) or can be called from a subroutine.

Excel Workbook
	F	G	H
1	Date/Time		Max-Min (Hrs)
2	6/23/2019 19:08		1971.6
3	7/8/2019 2:06
4	7/30/2019 15:36
5	7/5/2019 13:11
6	8/10/2019 11:40
7	8/21/2019 0:50
8	8/13/2019 4:54
9	6/7/2019 8:24
10	6/26/2019 7:33
11	7/26/2019 1:25
12	6/15/2019 22:37
13	7/12/2019 0:23
14	7/20/2019 7:59
15	7/27/2019 8:23
16	8/23/2019 18:45
17	6/20/2019 15:24
18	8/28/2019 12:00
19	8/27/2019 18:06
Sheet7

Function HoursAndMinutes(R As Range) As Double
'returns the duration between max and min times in range R, in hours
Dim Maxx, Minn
Maxx = Application.Max(R)
Minn = Application.Min(R)
HoursAndMinutes = (Maxx - Minn) * 24
End Function