Rounding time using a Case() function

[KurtW]

Hi all:

I want to increment the duration of a trip in .15 min blocks for billing. I am currently using this equation:

Hour(TripTimeCalc) +

(

Case ( Minute(TripTimeCalc)/60 >= 0 and Minute(TripTimeCalc)/60 <= 14 ; 0 ;

Minute(TripTimeCalc)/60 >= 15 and Minute(TripTimeCalc)/60 <= 29 ; 15 ;

Minute(TripTimeCalc)/60 >= 30 and Minute(TripTimeCalc)/60 <= 44 ; 30 ;

Minute(TripTimeCalc)/60 >= 45 and Minute(TripTimeCalc)/60 <= 59 ; 45 ; )

)

... but it returns 0 mins no matter what is fed in. (I should also mention that the field TripTimeCalc is derived using:

If

( DayBreak = "No" ;

ActReturnOrig - ActDepartOrig

;

(

(GetAsTime ( "11:59:59 PM" ) - ActDepartOrig) + ActReturnOrig - GetAsTime ( "12:00 AM" )

)

)

... FWIW

Can anyone see what I may have done wrong? Can you use 'AND' to add an argument to a Case() function?

Thanks

Kurt

[transpower]

Don't divide by 60.

[Lee Smith]

Hi Kurt,

I'll toss this out and fully expect -queue- or Ugo to shorten it.

Int((Time Entry + 899) / 900) * 900

Lee

[KurtW]

Transpower - why would I drop the division by 60? Isn't that what's giving me my minutes? After I removed the division I got some seriously weird results. Not sure if this is the way to go.

Lee - I must admit I'm still trying to figure out what your equation does, but I'll keep knocking on it.

Thanks

[Søren Dyhr]

Well I can sus the logics behind you calc' that goes:

Case ( Minute(TripTimeCalc)/60 >= 0 and Minute(TripTimeCalc)/60 <= 14 ; 0 ;

Minute(TripTimeCalc)/60 >= 15 and Minute(TripTimeCalc)/60 <= 29 ; 15 ;

Minute(TripTimeCalc)/60 >= 30 and Minute(TripTimeCalc)/60 <= 44 ; 30 ;

Minute(TripTimeCalc)/60 >= 45 and Minute(TripTimeCalc)/60 <= 59 ; 45 ; )

...But I have no wish to debug it, but instead suggest following:

Div ( Minute ( TripTimeCalc ) ; 15 ) * 15

Which should do what you're after. But I noticed that you add the quarter of hour directly to the hour, which is like mixing apples and bananas. You need to make it a text concatanation with a delimiter or make each hour 4 quarters before adding them.

When it comes tothe last part of your calc's are your logic behind it probably what you'll reduce to:

ActReturnOrig - ActDepartOrig + If(DayBreak = "No";0;43199)

...when applying the associative law from you way back algebra lessons as well as knowledge to how filemaker handle dates and time ...here is one entire day stored as integers from 0 to 86400. So 43199 is a sec' to noon.

Finally more does the version of OS/Filemaker version I have at hand treat the postfixing with AM/PM inside a GetAsTime( as a ? ...which can't be relied on in dependant calcs'

--sd

[KurtW]

Thanks Soren:

I think you hit the nail on the head with the 'adding apples to bananas' thing. I can see that is definitely the error in at least part of this.

I think I am close, but can probably adapt some of what you said if I fail.

Thanks for the help.

Kurt

[KurtW]

Okay, here

[-Queue-]

You can shrink your second calc to

Hour(TripTimeCalc) & "." & 25 * Div( Minute(TripTimeCalc); 15 )

[KurtW]

I think that's what Soren was trying to tell me, also. I think I see the logic and am going to try and break it down.

Thanks, Queue.

[transpower]

Hour(TripTimeCalc) &

(

Case ( Minute(TripTimeCalc) <= 14 ; 0 ;

Minute(TripTimeCalc) <= 29 ; .25 ;

Minute(TripTimeCalc) <= 44 ; .5 ;

.75 )

)

The above is just for clarity. Use -Queue-'s function--simpler is better.