1 2 Previous Next 16 Replies Latest reply on May 1, 2011 4:49 AM by LaRetta_1

# Failing to round time up

### Title

Failing to round time up

### Post

I'm using "round(aCalcFieldExpressingMM)" but the result is not rounding up. Does round not work with time values? The field I'm using expresses value only in minutes and even if the value is 14:55, when using round it still says "14" when I select "mm" in the inspector. I have also tried Ceiling to similar avail.

Using FM11, Snow Leopard, latest updates.

• ###### 1. Re: Failing to round time up

Try this calculation, result time:

Time ( Hour ( YourTimeField ) ; Minute ( YourTimeField ) ; Round ( Seconds ( YourTimeField ) ; - 1 ) )

• ###### 2. Re: Failing to round time up

Hmm. It's not changing the result, even if I alter the precision.

• ###### 3. Re: Failing to round time up

"The field I'm using expresses value only in minutes and even if the value is 14:55..."

What do you wish to round, minutes or seconds ?

• ###### 4. Re: Failing to round time up

I want to round seconds to minutes.

If the result of my calc is 0:13:44, but I express the field as 'mm', the time shows up as 13, whereas I want it to read 14.

• ###### 5. Re: Failing to round time up

In general, each time “level” (year, month, day, hour, minute, second) is a discrete entity and won’t round up to the next level.  So, you first need to convert all “levels” to the smallest unit in the calculation – in your case, minutes to seconds.  Then you divide the whole shebang by 60 (seconds/minute) to get just minutes.

To get rounded minutes you could use something like this in your calculated field with Calculation Result of Time:

Let ( [MinuteSecs = Minute (YourTimeField) * 60 ; Secs = Seconds (YourTimeField)] ;  Round (MinuteSecs + Secs / 60 ; 0) )

• ###### 6. Re: Failing to round time up

Superb—and I get a lesson in variables into the bargain. Will report back once it's in.

• ###### 7. Re: Failing to round time up

Hmm. Something's not right about the formula. I made a quick table to demonstrate:

prepSessionCueQuotient is the result of a calc that I want to express as 'mm' and round up if the seconds are 30 or above.

prepSessionCueQuotientRound is Krancmm's formula outputting a time.

prepSessionCueQuotientSecs is a duplicate of prepSessionCueQuotient but whose calculation results in a number, not a time.

prepSessionCueQuotientRoundSecs is a duplicate of prepSessionCueQuotientRound but whose calculation results in a number, not a time.

Any ideas?

This is the formula I'm using in prepSessionCueQuiotientRound:

Let ( [MinuteSecs = Minute (prepSessionCueQuotient) * 60 ; Secs = Seconds (prepSessionCueQuotient)] ;  Round (MinuteSecs + Secs / 60 ; 0) )

THANKS AGAIN

• ###### 8. Re: Failing to round time up

Howdy,

Not quite ceratin of the order of precedence, but perhaps try explicitly adding the MinuteSecs and Sec first then dividing ... Round((MinuteSecs + Secs)/60;0)

Cheers!
Lyle

• ###### 9. Re: Failing to round time up

Update: just found this in the FMP Help

( )
Precedence
FileMaker Pro evaluates formulas from left to right, performing multiplication and division before addition and subtraction. Using parentheses lets you change the order: FileMaker Pro evaluates expressions between parentheses first.

In your formula, it is doing the division first, then the addition :)

• ###### 10. Re: Failing to round time up

Well, if you want to always round up to the full minute then you could try:

Ceiling (  yourTimeField / 60 ) * 60

Result is time.  Am I missing something else here?

UPDATE: If you don't want to display the seconds at all then you can use Inspector and set the time display (data tab) to hhmm.

If you don't want it to display hours if over 60 minutes then use: Ceiling (  yourTimeField / 60 ) and set the result to number.

• ###### 11. Re: Failing to round time up

LaRetta,

Thanks for that. It works great for that, only I want 30 seconds to be the threshold. For some reason Round doesn't seem to play well with times.

• ###### 12. Re: Failing to round time up

Please give me the specific rule.  If it is 0:3:29, you want it to round down to 0:3:00 but if it is 0:3:30 you want it to round up to 0:4:00 ?

• ###### 13. Re: Failing to round time up

Thanks again. Your example is perfect.

• ###### 14. Re: Failing to round time up

I take shortcuts.  The t is your time field.  Set result as time.

UPDATE: If there are no seconds, my using < 30 still rounds the minutes down which is incorrect.  I adjusted the calculation to:

Ceiling (  t / 60 ) * 60 - 60 * ( Seconds ( t ) and Seconds ( t ) < 30 )

The theory, round all the times up to full minute then deduct a minute if seconds are under 30.  Portion in blue is boolean test which produces true if there is any number in Seconds ( t ).

"For some reason Round doesn't seem to play well with times."

Rounding is based upon Base 10 (decimal) instead of Sexagesimal.

1 2 Previous Next