DateDiff Conversion
in [Queries]
|
Prev: using dates
Next: Multi List combo as criteria
From: John W. Vinson on 25 Mar 2010 02:44 On Wed, 24 Mar 2010 20:55:08 -0700, "Mr. Bud" <No_Spam_chief123101(a)yahoo.com> wrote: >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 W. Vinson [MVP]
From: John Spencer on 25 Mar 2010 09:01 ..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 Mr. Bud wrote: > "John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message > news:vpalq5t406ebc8qe82l37v0mmlbkuirhbu(a)4ax.com... >> On Wed, 24 Mar 2010 16:27:57 -0700, "Reggie" >> <No_Spam_chief123101(a)yahoo.com> >> wrote: >> >>> 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 >> >> -- >> >> John W. Vinson [MVP] > > 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 ;-). >
From: Mr. Bud on 30 Mar 2010 03:08
"John Spencer" <spencer(a)chpdm.edu> wrote in message news:%23Ve%23CtBzKHA.5040(a)TK2MSFTNGP02.phx.gbl... > .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 > > Mr. Bud wrote: >> "John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message >> news:vpalq5t406ebc8qe82l37v0mmlbkuirhbu(a)4ax.com... >>> On Wed, 24 Mar 2010 16:27:57 -0700, "Reggie" >>> <No_Spam_chief123101(a)yahoo.com> >>> wrote: >>> >>>> 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 >>> >>> -- >>> >>> John W. Vinson [MVP] >> >> 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 & John, Thanks very much for your time. Exactly the info I was looking for. I'm starting to catch on a little. Sorry I didn't get back earlier but was out of town last week. Thanks again!! -- Reggie |