Date Time Group Math

[Guest]

I am trying to get the number of hours between date/time groups, ie:
2007-10-26 17:50 to 2007-10-28 07:45 is 37:55. I found out that if the
total hours is less that 24 then it is correct, but after that I get a
date/time group from the year 1900. Is there any way to compute these hours
correctly in Access 2000 or Access 2007 ( I have and use both)

My Data:
[dtg start] [dgt end]
Differnce
2007-10-26 17:50:00 2007-10-28 07:45:00 37h 55m
2007-10-26 17:50:00 2007-10-27 06:35:00 12h 45m

My Output:
total total2 total3 total4
13:55 38 2 1900-02-17 13:55:00
12:45 13 1 1900-01-23 12:45:00

total = [dtg end]-[dtg start] format short time
total2 = datediff('h',[dtg start],[dtg end],1,1) format gen number
total3 = datediff('d',[dtg start],[dtg end],1,1) format gen number
total4 = (DateDiff('d',[dtg start],[dtg end],1,1)*24)+([dtg end]-[dtg
start]) format dd hh:nn

My Query:

SELECT Time.[TimeID], Time.[DTG Start], Time.[DTG End], [dtg end]-[dtg
start] AS total, DateDiff("h",[dtg start],[dtg end],1,1) AS total2,
DateDiff('d',[dtg start],[dtg end],1,1) AS total3, (DateDiff('d',[dtg
start],[dtg end],1,1)*24)+([dtg end]-[dtg start]) AS total4
FROM [Time];

 

[Michael Gramelspacher]

Not sure what output you want, but one way to show elapsed time is

?RIGHT("0"+(CSTR(datediff("n",#2007-10-26 17:50:00#,#2007-10-28
07:45:00#)\60)),2)+"h "+RIGHT("0"+CSTR(datediff("n",#2007-10-26
17:50:00#,#2007-10-28 07:45:00#) Mod 60),2)+"m"
37h 55m

?RIGHT("0"+(CSTR(datediff("n",#2007-10-26 17:50:00#,#2007-10-27
06:35:00#)\60)),2)+":"+RIGHT("0"+CSTR(datediff("n",#2007-10-26
17:50:00#,#2007-10-27 06:35:00#) Mod 60),2)
12:45

 

[Douglas J. Steele]

Take a look at http://www.accessmvp.com/DJSteele/Diff2Dates.html

 

[Guest]

Thanks that did the trick. Maybe you can help me with another problem that I
am having. I have posted a question in the MS TechNet IIS General labeled:
noobie Error Connecting to Access DB.

Take a look at http://www.accessmvp.com/DJSteele/Diff2Dates.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am trying to get the number of hours between date/time groups, ie:
2007-10-26 17:50 to 2007-10-28 07:45 is 37:55. I found out that if the
total hours is less that 24 then it is correct, but after that I get a
date/time group from the year 1900. Is there any way to compute these
hours
correctly in Access 2000 or Access 2007 ( I have and use both)

My Data:
[dtg start] [dgt end]
Differnce
2007-10-26 17:50:00 2007-10-28 07:45:00 37h 55m
2007-10-26 17:50:00 2007-10-27 06:35:00 12h 45m

My Output:
total total2 total3 total4
13:55 38 2 1900-02-17 13:55:00
12:45 13 1 1900-01-23 12:45:00

total = [dtg end]-[dtg start] format short time
total2 = datediff('h',[dtg start],[dtg end],1,1) format gen number
total3 = datediff('d',[dtg start],[dtg end],1,1) format gen number
total4 = (DateDiff('d',[dtg start],[dtg end],1,1)*24)+([dtg end]-[dtg
start]) format dd hh:nn

My Query:

SELECT Time.[TimeID], Time.[DTG Start], Time.[DTG End], [dtg end]-[dtg
start] AS total, DateDiff("h",[dtg start],[dtg end],1,1) AS total2,
DateDiff('d',[dtg start],[dtg end],1,1) AS total3, (DateDiff('d',[dtg
start],[dtg end],1,1)*24)+([dtg end]-[dtg start]) AS total4
FROM [Time];