Help with Time of day minus Time of day

[jcaptchaos2]

I have this formula that takes the stop time and subtracts the start time which works fine if the the times are 6am and 3pm but if the start is 10pm and the stop is 1am it comes out wrong. Here is what I got right now.

[StopTime]-[StartTime]

 

[borgem]

it should be as simple as using an if statement to determine if the StopTime occurs "before" the StartTime, then use some clever algebra to use a different equation for such instances. i.e. ([24:00:00]-[StartTime])+([StopTime]-[00:00:00])

This would obviously vary based on your time format, but the general principal is there.

 

[boblarson]

The OPTIMAL is to always store the Date AND Time and not just the time. Then it is not a problem at all.

 

[jasmith4]

I think what's going on is that the fields are actually text fields, not date/time fields.
What happens with CDate([StopTime])-CDate([StartTime])?

 

[boblarson]

I think what's going on is that the fields are actually text fields, not date/time fields.
What happens with CDate([StopTime])-CDate([StartTime])?

It would be a negative amount and wrong like they say. So, since it goes over the date line we would need to use

IIf([StopTime]-[StartTime] < 0, [StopTime] - [StartTime] + 12, [StopTime] - [StartTime])

 

[jasmith4]

If they're really date/time fields, then this is also a data-entry or input-masking issue: the mask allows a user to type in only a time, whereupon the system assumes the current date at data-entry time. Presumably both start and end time are entered within seconds of each other, so unless the data entry is done very, very close to the stroke of midnight, both times will have the same date. If the time data are imported from some file which has only hour and minute information, the same assumption is made: current date. That is, you're right, Bob, there has to be a way of entering "yesterday at 10pm" and then "today at 1am" to avoid issues.

But I have a question about your formula: if they really are date/time fields, then the result of subtracting them is a floating-point number representing days or fractions thereof, right? If so, I believe your formula should be revised to:

[StopTime]-[StartTime] + IIf([StopTime] < [StartTime], 0.5, 0)

 

[jcaptchaos2]

Hey guys, thanks for the reply's, sorry for my late response, I am changing it to like you said have the date and time as I am a novice at Access and this seems to make it easy to understand, thanks.