Calculate Date and Time in hours

[mastolar]

Hello, is there a way in VBA to calculate the the time span, in hours, between two dates with both a day and time?

For instance I have four fields (with data):
startdate (01/01/07)
starttime (09:00:00 am)
enddate (01/03/07)
endtime (2:00:00 pm)

duration = 54:00 (hours/minutes)

What would be a calculation to come up with the duration? I haven't been able to find anything that calculates with both day and time. Any help is much appreciated! Thanks!

 

[Crowley16]

yes, F1 for datediff

--------------------
Procrastinate Now!

 

[mastolar]

Thanks Crowley - i just didn't know how to enter the syntax for datediff with both day and time fields. After a little playing I put in as:

dim tstart, tend as string
tstart = starttate & " " & starttime
tend = endtate & " " & endtime

dur = DateDiff("h", tstart, tend)

Thanks!

 

[Melagan]

datediff("h", [startdate] + [starttime], [enddate] + [endtime])

That should do.


~Melagan
______
"It's never too late to become what you might have been.

 

[SkipVought]

Date/Time is one data type.

Date/Time are just NUMBERS.

Date is the INTEGER part.

Time is the FRACTIONAL part.

The UNITS of Date/Time is DAYS.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Melagan]

Hence:

startdate (01/01/07)
starttime (09:00:00 am)

startdate = 39083
starttime = 0.375

~

startdate + starttime = 39083.375
.. = 01/01/07 09:00:00 AM

You can see this at work by converting dates to Double.
cdbl(startdate + starttime) = 39083.375
etc.


~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

Bottom line: why using TWO fields for storing a SINGLE value ?

 

[mastolar]

SkipVought and Melagan - Thanks for the info. I had not thought about using the date values.

PHV - I didn't elaborate at all - I need a seperate time field because the time more than likely will not be the same time the record is entered. It's a problem ticket kind of record. For instance the record may be entered at 10:00am but the problem actually started at 5:00am. I don't know how else to define that.

 

[SkipVought]

What happens if the problem is reported on 7/18/2007 at 01:00 but it actually happened several hours erlier at 21:00 on 1/17/2007,?

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[mastolar]

Then we would just change the startdate/time. It's not a 'set in stone' kind of date. It's just used to track mainly when a issue started - which is very possible that it could be entered incorrectly the first time.
I'm just trying to go one step further and give them an auto calculated duration so management doesn't have to get out thier calculators to figure it out.