how to calculate time between "PM" and "AM"

L

Lost

when I enter a time from AM to PM in my timesheet dbase it yields the desired
results. However, when I enter at time from PM to Am the results is always a
negative value. My question is how do I get the time elapsed between a PM to
an AM time difference.
 
K

Ken Snell [MVP]

One easy way is to store both the date and time together (the Now() function
will give you the current date and time), then you can use the DateDiff
function to calculate the elapsed time.

Otherwise, you'll need to use some type of expression that compares the two
times and corrects the result. For example:
TimeElapsedInFractionalHours = (TimeEnd - TimeStart -
(TimeEnd<TimeStart)) * 24
 
R

Rick Brandt

Lost said:
when I enter a time from AM to PM in my timesheet dbase it yields the
desired results. However, when I enter at time from PM to Am the
results is always a negative value. My question is how do I get the
time elapsed between a PM to an AM time difference.
Click to expand...

An Access DateTime always has both a date and a time. When you enter a date
without a time the time defaults to midnight. When you enter a time without a
date then the date defaults to 12/30/1899. So when you look at the difference
between a PM time and an AM time (both with no date in your eyes) you are
actually comparing those two times on 12/30/1899 and PM always comes after AM on
the same day.

You can test for when the end time is actually before the start time and then
add 1 day to the end time before calculating the difference.
 
G

George Nicholson

(((1+ TimeValue(dtmend) - TimeValue(dtmstart))*1440) Mod 1440)/1440

AFAIK, this should work as long as the difference between Start and End is <
24 hours.

- TimeValue() strips any date information from the argument.
- Time is the decimal portion of a number where 1 = one day: 0.0 = midnight;
0.5 = 12 noon; 0.75 = 6 pm; etc
- 1440 is the number of minutes in a day
- Adding 1 to EndTime adds a full day to the value, ensuring it is always
"greater" than start time (this addresses midnight wrapping issues)

- Mod returns the remainder after division
*VBA's Mod converts arguments to whole numbers before it does division,
so we need to convert our decimal time values first to get accurate results
*The result of our Mod statement gives us the # of hours difference
regardless of midnight wrapping (because it removes the day info we added).

- Convert that whole number back to a time decimal. Format as desired.

(FWIW, this isn't as convoluted in an Excel worksheet because the Mod
worksheet function doesn't convert arguments to integers before division)
=Mod(End-Start,1)

HTH,
 
Top