Time Calculation

[thelude]

Ok-
I've been reading threads for the last 2 days on this subject but I've been unable to wrap my head around this problem.

I have two fields on my form

TimeIn and TimeOut
All events are stored in 24 hour time format. I need to subtract the TimeIn from the TimeOut to get the total time. Simple right? [TimeIn]-[TimeOut] works execpt when the time spans the 24 hour mark. (e.g. 23:30 - 02:30 = 21:00 when the value should be 2:00). The DateDiff fucntion works in the same way. The other problem is my forms are based on one day only. (for example if an event occurs at 23:40 and goes to 04:30 it is still considered to have happened during the date of 23:40, therefore there is only one 'date' on the form), the reason why DateDiff doesn’t work.

Any Ideas?

Nick

 

[willir]

When you track / capture the time, you should codsider capturing the date + time. The system will use use the interger part as the date and the decimal part as the time.

DateDiff will work using this approach.

If you decide not to use date + time, you will have to check to make sure the end time is larger. If the end time is smaller, mostly meaning that you have gone over the 12:00 midnight / 24:00 hr, you have to use a DateAdd to add one day to the time before using DateDiff.

 

[thelude]

Can you show me how you would do the DateAdd based on the end time being smaller?

 

[willir]

Dim StartTime as Date, StopTime as Date, intTime as Interger

StartTime = Me.StartTime
StopTime = Me.StopTime

If StopTime < StartTime Then
   StopTime = DateAdd("d", 1, StopTime)
End If

intTime = DateDiff("n", StartTime, StopTime)

Here is a screen capture of a debug / immediate window session replaying the above...

stopt = #01:30:00#
?stopt
1:30:00 AM 
startt = #11:30:00#
?stopt
1:30:00 AM 
?datediff("n", startt,stopt)
-600 
stopt = dateadd("d", 1, stopt)
?stopt
12/31/1899 1:30:00 AM 

?datediff("n", startt,stopt)
 840

This is 840 min from 11:30 AM to 1:30 AM on the following day

Richard