Round result of calculated field, display as time

[lokimann]

Got myself stumped, can any of you Filemaker wizards help us out?

Question:

As part of a project billing db, I've got a starting time field, a stop time field, and a result field that shows the difference. All good so far.

What I'd like to do is round the resulting time to the next 15 minute increment.

I found the Round function in the Calculation dialog, I just couldn't figure out how to implement it. Help, please!

Bonus question:

The start, stop and result fields are in another file that is shown in a portal. This lets me enter unlimited instances of time periods for each project. (So I can do bits of the project from time to time, and it still shows on the same project.)

How do I total the times from the multiple results fields in the second file(only the ones that relate to that project) and show them in the record of the project?

Thank you for your time, I appreciate any help.

Peace

[Razumovsky]

As luck would have it, I actually just encountered the exact problem. Literally- closest 15 minutes.

Calc field "RoundedStartTime" formatted as time:

case(mod(starttime, 900)>=450, starttime+900-mod(starttime, 900),

mod(starttime,900)<450, starttime-mod(starttime, 900))

900= 60 (secs)*15, thats 15 minutes in formatted time.

mod(starttime, 900) will give you the remainder when dividing the time by 15v minutes. case statement decided whether to round up or down.

Going for the bonus round...

Not quite sure of your relational structure:

I assume you have a unique ID for each project that is never altered. Just set a relation from master to child files uniqueID::uniqueID

then make calc in time file: "HoursWorked" (endtime-starttime)

and make calc in master project file sum(uniqueID::HoursWorked).

Let me know if it is not clear.

HTH,

-Raz

[Vaughan]

A big trap here is to remember to work with start and finish dates as well as time in your calculations.

Time is stored as the number of seconds since midnight. You could have a start time of 11:59:59 pm and an end time of 12:00 am, and your calc will return an elapsed time of 11 hours 59 minutes and 59 seconds, not 1 second! Include start and end dates in the calc and it resolves itself.

To return the number of seconds elapsed the calculation should look something like:

(FTime - STime - ((FDate - SDate) * 24 * 12 * 60))

where F=finish, S=start.

[lokimann]

Thanks!

Now I just have to figure out how to implement that code you've included. Still a bit of a newbie.

I'm going to go see if I can get it to work.

I'll post back

Thanks for the quick reply.

[BobWeaver]

Once you get your duration as a time value (ie, seconds ) use this formula to round it:

Round(Duration/900,0)*900

So adding it to Vaughan's formula will give you:

Round((FTime - STime - ((FDate - SDate) * 24 * 12 * 60))/900,0)*900

[RussBaker]

Have a look at my file under the Handy Bits post inthe Samples section of this forum (go to the end to download the latest version). Under the Time tab there are several treatments of time intervals including the ability to:

. round to the nearest 0.xx of an hour

. round to the next (up) 0.xx of and hour

. round to the previous (down) 0.xx of an hour

These all use a combination of date and time - as per Vaughan's post which reminded us that there is more to an interval than just the time fields.

[lokimann]

For all the help, thanks!

This FileMaker Db thing is addictive and fun!