FORMAT to show minutes and seconds?

  • Thread starter Thread starter Maury Markowitz
  • Start date Start date
M

Maury Markowitz

Maybe this just doesn't work, but:

startTime = 12:30:08 PM
stopTime = 12:34:21 PM

Format((stopTime - startTime), "h:m:s") = "0:4:13"

Format((stopTime - startTime), "m:s") = "12:13"

Huh? Is there any way to display a minutes/seconds format? The task
I'm timing typically takes about 4 to 6 minutes, I don't want hours to
show up.

Maury
 
Since both month and minute start with m, one of them had to get changed.
Use n instead:

Format((stopTime - startTime), "n:s")

(Of course, that's not really how times are intended to be used in Access.)
 
The Date data type is intended to be used as a timestamp (a complete date
and time). Under the covers, they're eight byte floating points number,
where the integer portion represents the date as the number of days relative
to 30 December, 1899, and the decimal portion represents the time as a
fraction of a day. You can easily see this by applying a complete format to
a time field:

?Format(#08:00:00 AM#, "yyyy-mm-dd hh:nn:ss")
1899-12-30 08:00:00

When dealing strictly with times, you'll run into problems when they cross
midnight. For instance, to the average person, 11:30:00 PM to 1:30:00 AM is
2 hours, but check what Access thinks:

?Format(#1:30:00 AM# - #11:30:00 PM#, "hh:nn:ss")
22:00:00

That's actually misleading, since #1:30:00 AM# - #11:30:00 PM# results
in -0.916666666666667

Doing the opposite calculation, you'll see what I mean:

?Format(#11:30:00 PM# - #1:30:00 AM#, "hh:nn:ss")
22:00:00
?#11:30:00 PM# - #1:30:00 AM#
0.916666666666667

To get it correct, you need to take the date part into account:

?Format(#2008-04-22 1:30:00 AM# - #2008-04-21 11:30:00 PM#, "hh:nn:ss")
02:00:00
 
Back
Top