Time Elapse

[Richelle]

I have read some wonderful threads in various sites that have helped me with
manipulating time elapse between a Time In and Time Out field, but I am still
having one major issue. Let's say my Time In begins at 11:00 PM and the Time
Out is for 12:00 AM, it does not see the one hour difference, instead a 23
hour interval. Same for when Time In is 7:00 PM and Time Out is 12:00 AM, the
interval calculates at -19:00. So, I tried using an unbound field that looked
at the Time Out field and said if it (Time Out) = "12:00 AM" then take the
interval (-19 from last example of 7:00 PM - 12:00 AM) and subtract 24,
(-19-24 should = 5) but it gives me an #error in the unbound field.
Any comments would be greatly appreciated

 

[Pieter Wijnen]

24 Hrs = 1
The Time is stored as a fraction
ie
1 Hr = 1/24

HtH

Pieter

 

[John W. Vinson]

I have read some wonderful threads in various sites that have helped me with
manipulating time elapse between a Time In and Time Out field, but I am still
having one major issue. Let's say my Time In begins at 11:00 PM and the Time
Out is for 12:00 AM, it does not see the one hour difference, instead a 23
hour interval. Same for when Time In is 7:00 PM and Time Out is 12:00 AM, the
interval calculates at -19:00. So, I tried using an unbound field that looked
at the Time Out field and said if it (Time Out) = "12:00 AM" then take the
interval (-19 from last example of 7:00 PM - 12:00 AM) and subtract 24,
(-19-24 should = 5) but it gives me an #error in the unbound field.
Any comments would be greatly appreciated

An Access Date/Time field is stored as a double float number, a count of days
and fractions of a day since midnight, December 30, 1899. As such a pure time
value is stored as a time on that long-ago day. Midnight (at the start of)
December 30, 1899 was in fact 19 hours prior to 7 PM on that day!

Consider storing both the date and time of your Time IN and Time OUT fields;
so rather than storing 7:00pm, store #10/17/2007 7:00pm# (using Now() rather
than Time() to fill it automatically, if you're doing that). Then you can use
DateDiff() to calculate the time elapsed. Subtracting dates to get a date/time
duration is possible but VERY limited - summing work time will display odd
results if the sum exceeds 24 hours, since it will treat (say) 30:00 hours as
a point in time, 6 AM on December 31, 1899 to be exact, and show you 6:00
instead of 30 hours!

John W. Vinson [MVP]

 

[Michael Gramelspacher]

Myself, I would follow John Vinson's advice, but here is an old tip by
John Viescas.

CalcTime: DateDiff("n", [TimeIn], IIF([TimeOut]<[TimeIn], [TimeOut] +
1,[TimeOut]))

Try in immediate window:

?DateDiff("n",#11:00 PM#,iif(#12:00 AM# < #11:00 PM#,
dateadd("d",1,#12:00 AM#),#12:00 AM#))
60
?DateDiff("n",#07:00 PM#,iif(#12:00 AM# < #07:00 PM#,
dateadd("d",1,#12:00 AM#),#12:00 AM#))
300