Access Time Diff Woes

[jfrost10]

Hey guys,

I'm having some issues getting the time difference between two values.

Two dates:
05/10/2006 10:00:00 AM (we'll call this date1)
06/10/2006 10:21:00 AM (we'll call this date2)

The output I need is 24:21

Here's what I'm experiencing:

DateDiff("Short Time",[date1],[date2])
And I get
#Error

Format(([CBTime])-([Time Arrival]),"hh:nn:ss")
And I get
00:21:00

DateDiff("h",[CBTime],[Time Arrival])
And I get
24 (was hoping it might fractionalize the remainder)

Is there any way to get the output I want without having to strip the time into pieces and manually calculate?

Thanks,

D

 

[jfrost10]

ok, so one other thing I've tried:

DateDiff("n",[date1],[date2])/60

Which will give me a fractional value:
So 1h 15min = 1.25

But I need this still shown as
1:15

There's gotta be an easier way than resorting to vba...

D

 

[PHV]

you may try this:
(24*Int(date2-date1)+Format(date2-date1,"h")) & Format(date2-date1,":nn")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MichaelRed]

there are several to numerous (perhaps even MANY) threads re various date time related formats and pecularities. Please feel free to peruse the existing body of information, with the aim of obtaining insight into the fundamental nature of the calculation you are attempting.

I would suggest 'Advanced' search of these fora (Tek-Tips) with kewords:
[DOB | Age | AgeAsOf | DateFormat | DAte Format] et al. Whilst there are many additional keywords, these should (with various combinations and permutations) get you well along the path ...




MichaelRed

 

[TheAceMan1]

How are ya jfrost10 . . .

. . . and [blue]PHV's[/blue] post? . . .

See Ya! . . . . . .