Calculating elapsed time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

WKN - 20 Oct 2005 02:34 GMT

I am trying to calculate the elapsed time between between two time periods.
The begin time in the afternoon (PM) of one day and the end time is the
morning (AM)of the following day. Example
=HoursAndMinutes([TimeOut]-[TimeIn])
When I use the expression above for the time period Jan1, 2005 Begin Time
10:00 PM and End Time 2:00 AM (the following day) returns the answer "-20"
Please help
Wallace

Reply to this Message

John Vinson - 20 Oct 2005 06:12 GMT

>I am trying to calculate the elapsed time between between two time periods.
>The begin time in the afternoon (PM) of one day and the end time is the
[quoted text clipped - 4 lines]
>Please help
>Wallace

The DateDiff() function returns the difference between two date/time
values, correctly handling over midnight (or over many days, in fact);
you can get the time difference in any unit from seconds to years (as
an integer, not a Date/Time).

DateDiff("n", [TimeIn], [TimeOut])

will return the number of minutes; to display as hh:nn you can use

DateDiff("n", [TimeIn], [TimeOut]) \ 60 & ":" & Format(DateDiff("n",
[TimeIn], [TimeOut]) MOD 60, "00")

John W. Vinson[MVP]

Reply to this Message

WKN - 20 Oct 2005 10:22 GMT

Thanks for the assistance.
I tried the DateDiff() function. I got an answer of -20 (the correct answer
would be 4). Where am I going wrong?
Wallace

> I am trying to calculate the elapsed time between between two time periods.
> The begin time in the afternoon (PM) of one day and the end time is the
[quoted text clipped - 4 lines]
> Please help
> Wallace

Reply to this Message

Ed Warren - 20 Oct 2005 11:30 GMT

Sounds like you have the dates switched in the datediff() function
the answer you are getting is ( Jan 2, 2005 2:00am to Jan1, 2005 10pm )
= -20 hours.
what you want is DateDiff("h", EarlyDate, LateDate)

Ed Warren.

> Thanks for the assistance.
> I tried the DateDiff() function. I got an answer of -20 (the correct
[quoted text clipped - 12 lines]
>> Please help
>> Wallace

Reply to this Message

WKN - 20 Oct 2005 14:03 GMT

I am still getting the wrong answer (-20). I should point out that the table
I am using has the following fields: Employee, Date, Time In, Time Out,
Hrs/Mnts Worked.
Thanks
Wallace

> Sounds like you have the dates switched in the datediff() function
> the answer you are getting is ( Jan 2, 2005 2:00am to Jan1, 2005 10pm )
[quoted text clipped - 19 lines]
> >> Please help
> >> Wallace

Reply to this Message

Ed Warren - 20 Oct 2005 14:17 GMT

Check the Date for the TimeIn and TimeOut Fields. Sounds Like they do not
have the proper date and you are calculating the time from 0200-->1000

You don't need to store the Hrs/mins worked. That is a calculated field.
You also don't need to have a 'date' field.

You need:
Employee TimeIn, TimeOut (TimeIn and TimeOut should include both the date
and time, if they do then your calculations will be correct).
Eg. TimeIn (1/1/2005 10:00 pm) TimeOut (1/2/2005 02:00 am)

Ed Warren

>I am still getting the wrong answer (-20). I should point out that the
>table
[quoted text clipped - 28 lines]
>> >> Please help
>> >> Wallace

Reply to this Message

Douglas J Steele - 20 Oct 2005 14:48 GMT

Access doesn't have a Time data type: all it has is the Date data type,
which is intended to include both date and time (internally, it's stored as
an 8 byte floating point number, where the integer portion represents the
date as the number of days relative to 30 Dec, 1899, and the decimal portion
represents the time as a fraction of a day)

You can solve the problem by storing the date along with the time (you can
use the DateValue or TimeValue functions to extract just the date or just
the time should you need it elsewhere).

You could also take a look at
http://www.mvps.org/access/datetime/date0008.htm at "The Access Web"
(although I feel it's a mistake to only store time without date...)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> I am still getting the wrong answer (-20). I should point out that the table
> I am using has the following fields: Employee, Date, Time In, Time Out,
[quoted text clipped - 25 lines]
> > >> Please help
> > >> Wallace

Reply to this Message

WKN - 24 Oct 2005 02:57 GMT

Hey. Thank you all very much. I see where I was going wrong.
Thanks again
Wallace

> Access doesn't have a Time data type: all it has is the Date data type,
> which is intended to include both date and time (internally, it's stored as
[quoted text clipped - 42 lines]
> > > >> Please help
> > > >> Wallace

Reply to this Message