Simon Leung
asked on
TimeDifference in VBA
Any idea how to calculate time difference in vba ?
Suppose the login time is 9:20:00 am and I want to know the difference from 9:00:00am .
Thx
Suppose the login time is 9:20:00 am and I want to know the difference from 9:00:00am .
Thx
datediff("n",LoginTime,TheTimeyouwantToCheckDifference)
ASKER
Cells(i, 5).Value = DateDiff("n", Cells(i, 3).Text, "9:00")
The value in cells(i,3) = 7:44
The result is 76.0
It seems that it is not correct...
The value in cells(i,3) = 7:44
The result is 76.0
It seems that it is not correct...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
See the way below to get the difference of 2 date variables.
Dim diff As Date
diff = Now - #7/7/2016 2:15:16 PM#
Debug.Print CLng(Int(diff)) & " days"
Debug.Print Hour(diff) & " hours"
Debug.Print Minute(diff) & " minutes"
Debug.Print Second(diff) & " seconds"
A time difference or a duration or a time span is not a date.
A duration is normally an integer (Long) representing the difference in a needed time resolution like seconds or minutes.
A time span is a tuple of a start date and a duration or a start and end date or an end date and a duration (the last is not that common).
The difference is always calculated using the DateDiff() function, where you specify the needed resolution as the first parameter.
Date's are sometimes used to lazy format the difference, when calculated in seconds or minutes as hh:mm:ss. But this is formally wrong, cause a duration is NOT a date and when not done carefully, you'll get an "overflow" resulting in a date portion instead of dd:hh:mm:ss.
And you'll need in all cases a custom format function, when you want to represent the duration as correct string. Thus using the correct data types, makes life easier.
A duration is normally an integer (Long) representing the difference in a needed time resolution like seconds or minutes.
A time span is a tuple of a start date and a duration or a start and end date or an end date and a duration (the last is not that common).
The difference is always calculated using the DateDiff() function, where you specify the needed resolution as the first parameter.
Date's are sometimes used to lazy format the difference, when calculated in seconds or minutes as hh:mm:ss. But this is formally wrong, cause a duration is NOT a date and when not done carefully, you'll get an "overflow" resulting in a date portion instead of dd:hh:mm:ss.
And you'll need in all cases a custom format function, when you want to represent the duration as correct string. Thus using the correct data types, makes life easier.
Open in new window