DateDiff Conversion

[Reggie]

Hi and TIA. I have a query that is getting the datediff between to
date/timestamp fields. If the difference is 6days and 5 hrs I was wondering
if it is possible to have the result show as 6d and 5h. Thanks!

 

[John W. Vinson]

Hi and TIA. I have a query that is getting the datediff between to
date/timestamp fields. If the difference is 6days and 5 hrs I was wondering
if it is possible to have the result show as 6d and 5h. Thanks!

Not directly with DateDiff, without some extra coding; but... See Doug Steele
and Graham Seach's Diff2Dates function:

http://www.accessmvp.com/djsteele/Diff2Dates.html

 

[Mr. Bud]

Not directly with DateDiff, without some extra coding; but... See Doug
Steele
and Graham Seach's Diff2Dates function:

http://www.accessmvp.com/djsteele/Diff2Dates.html

John, Me again. Thanks for the link

 

[Mr. Bud]

Not directly with DateDiff, without some extra coding; but... See Doug
Steele
and Graham Seach's Diff2Dates function:

http://www.accessmvp.com/djsteele/Diff2Dates.html

John, me again. Got the function working with the link you sent . Now was
wondering if it's possible to get an average from the result? I keep
getting data type mismatch(which don't surprise me). My results are like:

21d 3h
15d 5h
etc.

Don't know if its possible but thought I'd ask. Thanks!

 

[Mr. Bud]

Not directly with DateDiff, without some extra coding; but... See Doug
Steele
and Graham Seach's Diff2Dates function:

http://www.accessmvp.com/djsteele/Diff2Dates.html

John, Basically what I'm trying to do and have been combing the
network/help files for is I have a totals query grouped by supplier. I have
a order date and delivery date. I'm trying to get an average time it takes
for the delivery in Days:Hrs. I'm getting a result like 25.6. I know the
25 is 25days. Question is what does the .6 mean. Is it 6/10 of an hr or 6
hours out of 24 or am I way off base here. Probably a dumb answer, but
never said I was the sharpest crayon in the box ;-).

 

[John W. Vinson]

John, me again. Got the function working with the link you sent . Now was
wondering if it's possible to get an average from the result? I keep
getting data type mismatch(which don't surprise me). My results are like:

21d 3h
15d 5h
etc.

"21d 3h" is not a number.... it's a text string. You can't take the average
of "21d 3h" and "15d 5h" any more than you can take the average of "Bud" and
"John".

To answer your other question, a Date/Time value is a Double Float number, a
count of days and fractions of a day (times) since midnight, December 30,
1899. Subtracting two dates will give you a number of the same type; e.g.
21.125 is 21 and an eighth days, that is, 21 days and 3 hours.

To display it in that way you could use an expression such as

Fix([duration]) & "d " & Fix(24*([duration] - Fix([duration]))

This will subtract 21 from the 21.125, multiply it by 24 to get hours, and Fix
will truncate the (possibly fractional) hours to a whole number.

 

[John Spencer]

..6 is .6 of 24 hours. So if you multiply .6 * 24 you will get 14.4 hours.
And if you want minutes then .4 * 60 = 24

Of course, you can cheat a little bit and use
Format(CDate(25.6),"hh:nn") and get a string back of "14:24"



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County