Converting number data to mm:ss format

  • Thread starter Thread starter cb
  • Start date Start date
C

cb

I have a column (Type - number,single,decimal:auto) that is storing seconds.
I need to reformat this information into mm:ss format. Thus far I have not
succeeded. A few permutations (122.49 entry!Fin_time for example):

TimeSerial - datatype mismatch error
Format$([entry]![Fin_time],"nn:ss") Returns data(45:36), but I not what I
need.

Do I have to switch the datatype first (I can't change the base table) or
something else?

Basically I want to end up with: 122.49 = 2:02.49 (mm:ss:ht)

Any help would be appreciated!

CB
PS. I am trying to do this in the Field line of the access query so it will
do all the times. I also have criteria in there to filter out 0's and nulls.
 
It looks as if you would need to do

122.51\60 & Format(122.51 mod 60,":00") & Format(122.51 - Int(122.51),".00")

Substitute your field for the 122.51 value.
 
Start with a field called [time in seconds] and use it
four times to derive hours, minutes, seconds, 1/100ths
====== see the following expressions:
Hours: Int(([time in seconds])/3600)
Minutes: Int(([time in seconds]-([Hours]*3600))/60)
Seconds: Int([time in seconds]-(([Minutes]*60)+([Hours]
*3600)))
Hundreths: [time in seconds]-(([Minutes]*60)+([Hours]
*3600))-[seconds]

Be sure you have the fields from left to right in Hours,
minutes, seconds, etc sequence

works like a champ. Use formatting catenation (&) and
(: .) to make into official looking 24:59:59.99 time.
Filtering should take care of itself with 0:0:0.0.

Good luck
 
Back
Top