Calculating elapsed time

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

On Wed, 19 Oct 2005 18:34:02 -0700, WKN
wrote:

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

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]

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

"WKN" wrote:

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

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.

"WKN" wrote in message
...
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

"WKN" wrote:

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

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

"Ed Warren" wrote:

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.

"WKN" wrote in message
...
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

"WKN" wrote:

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

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


"WKN" wrote in message
...
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

"Ed Warren" wrote:

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.

"WKN" wrote in message
...
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

"WKN" wrote:

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

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...)

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


"WKN" wrote in message
...
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

"Ed Warren" wrote:

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.

"WKN" wrote in message
...
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

"WKN" wrote:

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

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

"Douglas J Steele" wrote:

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...)

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


"WKN" wrote in message
...
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

"Ed Warren" wrote:

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.

"WKN" wrote in message
...
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

"WKN" wrote:

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