Try this calculation, result time:
Time ( Hour ( YourTimeField ) ; Minute ( YourTimeField ) ; Round ( Seconds ( YourTimeField ) ; - 1 ) )
Hmm. It's not changing the result, even if I alter the precision.
"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 ?
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.
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) )
Superb—and I get a lesson in variables into the bargain. Will report back once it's in.
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.
This is the formula I'm using in prepSessionCueQuiotientRound:
Let ( [MinuteSecs = Minute (prepSessionCueQuotient) * 60 ; Secs = Seconds (prepSessionCueQuotient)] ; Round (MinuteSecs + Secs / 60 ; 0) )
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)
Update: just found this in the FMP Help
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 :)
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.
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.
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 ?
Thanks again. Your example is perfect.
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.