Why does DateDiff return a date and not the number of minutes?

Question

I need to find how many minutes exist between two string.

h1 = TimeValue("06:00:00")
h2 = TimeValue("22:00:00")
res = DateDiff("n", h1, h2)

However, res = 17/08/1902 whereas the expected result is 960.

Sub calcul(hours As Variant, Optional n As Integer = 0)
    i = 3
    Do While (Cells(i, 0) <> "")
        Dim res As Date
        Dim h2 As Date
        Dim h1 As Date
        Dim h As Integer

        If (n = 0) Then
           h = 0
        Else
           h = Cells(i, 7).Value - 1
        End If

        h1 = TimeValue(hours(h)("h1"))
        h2 = TimeValue(hours(h)("h2"))
        res = DateDiff("n", h1, h2)

        ...

Answer 1

The problem here is how you you've defined res.

Dates and time values are numbers. Even if you see it as 30/09/2019 or 12:00:00, actually, for Excel, both cases are numbers.

First date Excel can recognize properly is 01/01/1900 which integer numeric value is 1. Number 2 would be 02/01/1900 and so on. Actually, today is 43738.

For times is the same, but the decimal parts are the hours, minutes and second. 0,5 means 12:00:00. So, actually, 43738,5 means 30/09/2019 12:00:00.

Anyways, in your case, you are obtaining time difference between 2 times in minutes. The result is 960, but you are asigning this value to a date type, so 960 is getting converted to 17/08/1902.

Dim h1 As Date
Dim h2 As Date
Dim res As Single


h1 = TimeValue("06:00:00")
h2 = TimeValue("22:00:00")
res = DateDiff("n", h1, h2)

Debug.Print res

The code above will return 960 properly. Adapt it to your needs.

UPDATE: Because DateDiff returns a Long, defining res as Single is not worth it at all. I did it because working with times, in many cases, needs decimals, but if you are using just DateDiff, then you can perfectly do res as Long or res as Integer.

Note the difference between DateDiff and a normal substraction with a simple code:

Dim time1 As Date
Dim time2 As Date
Dim res1 As Integer
Dim res2 As Single 'or double if you wish

time1 = "06:00:00"
time2 = "06:30:30"

'time difference between these 2 values are 30 minutes and 30 seconds (30,5 minutes in decimal)

res1 = DateDiff("n", time1, time2)
res2 = (time2 - time1) * 1440 '1440 is the number of minutes in a whole day

Debug.Print "With DateDiff:" & res1, "Normal: " & res2

The output of this code is: With DateDiff:30 Normal: 30,5

Using DateDiff sometimes is not worth it. Depending on how accurate you need the result, DateDiff may compensate or not. I would suggest you to avoid it if you can (this is jut my opinion)

Hope this helps

UPDATE 2: About the code above, yes, a solution would be using DateDiff("s", time1, time2) / 60 to get the seconds transformed into minutes, but this value, because of decimals, should be assigned to a data type that allows it.