I can't seem to get some of my cells to format correctly. I am trying to type
in minutes and seconds (mm:ss). Everytime I change it to that, the time
messes up. For example, it will only show something like 0:15 after I've
typed 28:15 or 4:04 after I've typed 24:04. Does anybody have any suggestions
on how to fix the problem?
You say "cells" - are you perhaps using Excel? Spreadsheets have cells; tables
don't.
A Date/Time field is stored as a Double Float number, a count of days and
fractions of a day (times) since midnight, December 30, 1899. Entering
anything in the format xx:xx will be interpreted as hours and minutes; you
must enter xx:xx:xx to get seconds as well.
In addition, times over 24 hours roll over into a new day (December 31, 1899
to be exact). I'm guessing that 28:15 is actually being displayed as 4:15 -
4:15 AM on that long-ago day.
For storing durations, I'd strongly suggest NOT using Date/Time fields AT ALL.
4 minutes 15 seconds should be stored as 255 seconds. You can display it using
an expression like
[duration] \ 60 & ":" & Format([duration] MOD 60,"00")
and enter it using a Form with two unbound textboxes for minutes and seconds;
use code in their AfterUpdate events to "push" the duration into a bound
control.
John W. Vinson [MVP]
