Time difference using seperate date and time fields

[Walter]

I have a database that tracks downtime for ATM machines. I have four seperate
fields:

Out Service Date
Out Service Time
In Service Date
In Service Time

These fields are manually entered into the db. I would like to calcualte the
total downtime using both the date and time fields.

In plain English: Difference between "Out Service Date" "Out Service Time"
and "In Service Date" "In Service Time"

Any help is greatly appreciated.

 

[Beth Melton]

Here's a link that contains several Date/Time calculation examples:
http://www.mvps.org/access/toc.htm#datetime

Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Office 2007 Preview Site:
http://www.microsoft.com/office/preview/default.mspx
Office 2007 Community Articles/Tutorials:
http://www.microsoft.com/office/preview/community/article_archive.mspx

TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/

 

[Bob I]

In Access a number field (DeadTime) updated using the following

(Table1!DateIn+[Table1!TimeIn])-(Table1!DateOut+[Table1!TimeOut])

will give you the days in a decimal format. Example:

DateOut TimeOut DateIn TimeIn DeadTime 8/28/2006 09:30:00
8/31/2006 19:30:00 3.416667

 

[Walter]

I was able to get the exact time in minutes by using:

=(DateDiff("n",[out service date],[in service date]))+(DateDiff("n",[out
service time],1))+(Abs(DateDiff("n",[in service time],1)-1440))-1440

Now my question is how do I make the final result, which is in minutes, in
the dd:hh:mm format. the format(above formula,"dd:hh:mm") does not work.

Thank you, knowledgeable people.



--
Walter

In Access a number field (DeadTime) updated using the following

(Table1!DateIn+[Table1!TimeIn])-(Table1!DateOut+[Table1!TimeOut])

will give you the days in a decimal format. Example:

DateOut TimeOut DateIn TimeIn DeadTime 8/28/2006 09:30:00
8/31/2006 19:30:00 3.416667

I have a database that tracks downtime for ATM machines. I have four seperate
fields:

Out Service Date
Out Service Time
In Service Date
In Service Time

These fields are manually entered into the db. I would like to calcualte the
total downtime using both the date and time fields.

In plain English: Difference between "Out Service Date" "Out Service Time"
and "In Service Date" "In Service Time"

Any help is greatly appreciated.

 

[Bob I]

The expression I provided, formatted to dd:hh:nn:ss will give you down
to seconds. Drop the ":ss" if all you want is minutes.

 

[Walter]

Your method was so much simpler. Thanks.
--
Walter

The expression I provided, formatted to dd:hh:nn:ss will give you down
to seconds. Drop the ":ss" if all you want is minutes.

I was able to get the exact time in minutes by using:

=(DateDiff("n",[out service date],[in service date]))+(DateDiff("n",[out
service time],1))+(Abs(DateDiff("n",[in service time],1)-1440))-1440

Now my question is how do I make the final result, which is in minutes, in
the dd:hh:mm format. the format(above formula,"dd:hh:mm") does not work.

Thank you, knowledgeable people.

 

[Bob I]

You're welcome.

 

[Walter]

One more thing. The formula works great is the issue was solved in less than
24 hours. If the period is longer than 24 hours, then the hh:nn is
inaccurate. The dd:hh:nn format is off. For example:

(08/14/06 + 11:39)-(08/11/06 + 13:15) = 01:22:24 in dd:hh:nn when it should
be 02:02:24

Any thoughts?

 

[Bob I]

Actually the "hours and minutes" part is correct(you're 2.5 hours short
of a day), but the "days" part is really wonky! I get a few flaky
results for the day part, anything from 1 to 31, almost like its not
really sure how many days there are. btw it should be "02:22:24" But I
never got that, only wierd results for the days

 

[Bob I]

Futzed with it some more and I don't understand whats happening with the
"DD" part. So try a different approach

For the full days of downtime
Fill in a seperate "Days" field formatted as Number
INT((Table1!DateIn+[Table1!TimeIn])-(Table1!DateOut+[Table1!TimeOut]))

For the Hours and minutes of downtime
Fill in Time field formatted as short time with
(Table1!DateIn+[Table1!TimeIn])-(Table1!DateOut+[Table1!TimeOut])

that way the days are correct and the hours and minutes are correct.

 

[Beth Melton]

No new thoughts - still the same as my previous post which contains a
link to various Date/Time functions which tackle various issues when
calculating date time.

Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Office 2007 Preview Site:
http://www.microsoft.com/office/preview/default.mspx
Office 2007 Community Articles/Tutorials:
http://www.microsoft.com/office/preview/community/article_archive.mspx

TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/