General discussion

Locked

visual basic date & time

By lukeu ·
How do I work out the number of hours spent from 2 fields that have a start date , time and date, time

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

visual basic date & time

by JimBb In reply to visual basic date & time

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

Collapse -

visual basic date & time

by lukeu In reply to visual basic date & time

The question was auto-closed by TechRepublic

Collapse -

visual basic date & time

by sl-campbell In reply to visual basic date & time

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)

Collapse -

visual basic date & time

by sl-campbell In reply to visual basic date & time

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)

Collapse -

visual basic date & time

by lukeu In reply to visual basic date & time

The question was auto-closed by TechRepublic

Collapse -

visual basic date & time

by lukeu In reply to visual basic date & time

This question was auto closed due to inactivity

Back to Web Development Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums