Time Conversion

[jsevilla]

I'm having a problem with time conversion.

Scope: I have form that I'm using to track time for functions performed. So for each function I have a start and stop time field, both of these fields are defined as text in the table.

Reason for this is my end user complained of difficulty in entering in times quickly when fields were set to date/time, with a short time format.



I take the text field and use the TIMESERIAL function to convert to hours and minutes. (i.e. 0900 is converted to 9:00). Then I have a second text box that I use the DATEADD function to add the length of time in minutes allotted for that step. (i.e. 9:00 + 90minutes is 10:30)
I then use conditional formatting on the start and stop fields to turn green if they are less than or equal to the time allotted or red if they go over.
Problem: Everything works fine if all the times are in the same 24 hour period. If I have a step that goes from say 2300 (11pm) to 0030 (1230am), the conditional formatting doesn't work properly, always stays green, even if 0045 is entered as the stop time.
Any help would be much appreciated.
John

[RuralGuy]

This link might help you: http://www.mvps.org/access/datetime/date0004.htm