6 replies
by jimbb
You do that using "datediff". I suggest you look that up in your help to get all of the possible parameters. A simple example:
datediff(h,FirstDate,SecondDate)
will give you the difference in hours between the two date-variables.
Jim
by lukeu
by sl-campbell
Assumming both fields are Date/time fields and variables are already assigned to the values of the fields you can use the following depending upon the amount of accuracy you need:
Dim lHoursDiff As Long
Dim sStartDateTime As String
Dim sEndDateTime As String
lHoursDiff = DateDiff("h",sStartDateTime,sEndDateTime)
("h" means hours here)
The variables for the start and end date/times can be a string or date variable.
HOWEVER, the results are not always what you would expect:
If:
sStartDateTime is 11.06.01 08:30:00 and the sEndDateTime is 11.06.01 08:40:00 then 0 will be returned.
And:
If sStartDateTime is 11.06.01 08:30:00 and the sEndDateTime is 11.06.01 09:30:00 then 1 will be returned.
HOWEVER:
If sStartDateTime is 11.06.01 08:30:00 and the sEndDateTime is 11.06.01 09:00:00 then 1 will also be returned.
As soon as a new hour turns over, the amount of hours increase by one, no matter if the time passed since the last hour was 60 minutes or not.
Therefore, you need to calculate the MINUTES and divide by 60:
lHoursDiff = DateDiff("n",sStartDateTime,sEndDateTime)/60
("n" means minutes here).
This will ofcourse also return fractions of an hour.
Therefore if you want only true FULL hours actually passed then you need to do the following using the Int() function:
lHoursDiff = Int(DateDiff("n",sStartDateTime,sEndDateTime)/60)
by sl-campbell
I need to add alittle more to the perceived inaccuracy of the DateDiff function. Perceived inaccuracy? Only when used wrong.
In my previous example, perceived inaccuracy can also be found: it has to do with the seconds. So, in-order to acheive the highest level of accuracy for this example one would need to calc. the seconds ("s") and divide by 3600.
The DateDiff function always chops off the date and time portions not needed. What is not needed is based upon what is requested. If I want to calculate years and pass two dates containing the month/day/year then the first thing that is done is the month and day parts are "chopped" off. THEN the calculation is made. So a date difference in years between the years 2001 and 2002 returns 1, and between 12/31/2001 and 01/01/2002 ALSO return 1 year even though only a day has passed. It would also return 1 year EVEN if only 1 second has passed.
Answer, If you want a 100% accurate answer then always request a DateDiff for the smallestunit that you are supplying, (supplying a date/time down to the second then use seconds "s" in the DateDiff function even if you want to calculate hours passed) and THEN calculate the unit desired, (seconds are now calculated but I need hours elapsed, so divide the seconds elapsed by the number of seconds in an hour)
by lukeu
by lukeu
This discussion is closed to new replies.
Trending Web Development posts
Sort by Trending