converting mm:ss to total ss

[kiwiora]

Hi guys,

I'm trying to set up a database to convert data entered as mm.ss (e.g 2.30) into total number of seconds (i.e 2 minutes = 60 seconds, plus 30 seconds = 150 seconds)

I have

Minute ( time ) * 60 + Seconds ( time )

but that means my user has to enter in 0:02:30

Edited September 19, 2005 by Guest

[RalphL]

I assume that the entry field will be a number and the calculation will be a number. Lets call the entry field Tms. Then Ts = Int (Tms) * 60 + Tms - Int (Tms)

The integer part is minutes * 60 gives seconds

The total - the integer gives the seconds.

[kiwiora]

hmmm thanks but unfortunately that's not right...

Int (tms) * 60 + tms - Int (tms)

If you put in 1.5 (being 1 minute 50 seconds or 110 seconds total) it views the .5 as half (i.e the result is 60.5)

so if you put the values in the equation.

Int (1) * 60 + 1.5 - Int (1) = 60.5

so we need to convert the .5 to 50 seconds??

[Søren Dyhr]

How about this:

Let(tt=Substitute ( entry ; "." ; "|" ) & "0";Abs(Time(0;LeftWords ( tt ; 1 ) ; Left(RightWords ( tt ; 1 ) ;2))))

--sd

[Raybaudi]

or about this:

(I assume that the entry field will be a text and the calculation will be a text. Lets call the entry field Tms.)

Let([

min = Left ( Tms; Length ( Tms ) - Position ( Tms ; "." ; 1 ; 1 ) );

sec = Right ( Tms; Length ( Tms ) - Position ( Tms ; "." ; 1 ; 1 ) )

];

min * 60 + If ( Length ( sec ) = 1; sec & "0"; sec )

)

[RalphL]

OK, a minor adjustment should fix this. Try the following:

Int (tms) * 60 + (tms - Int (tms)) * 100

Edited September 19, 2005 by Guest
Typo

[Raybaudi]

Hi RalphL

this is pretty !

[RalphL]

Thanks Daniele. Your English is fine.

[kiwiora]

thanks RalphL

i'm comparing mobile phone costs for my firm to evaluate the tenders we've received.

So i've sorted out all the calcs to switch between per second billing and 30 second billing.

i.e - the cost is given as per 30; but is billed per increment (1s or 30s)

The issue I"m now having is rounding is based on .5, but in the calc, I need it to round up to the second (excuse the pun) it goes over the set increment. i.e my calc is

Round ( seconds / carrier2increment ; 0 )

For example: seconds = 45 / 30 = 2 nice.

but 31 / 30 = 1 it should equal 2 as well as it is over a 30 increment

Hope that makes sense.... hope everyone's still looking into this post

Thanks in advance.

[RalphL]

Try using Ceiling instead of Round.

Ceiling ( seconds / carrier2increment )

[kiwiora]

You are my hero!! Thank you so much for all your help - ceiling is my new found friend

Now if only I can hunt down all the millions of other secret squirrels i'm yet to discover.

Thank you guys - you rock!