basic time and duration calculations

[dd3]

Help! I am a bit baffled about how Filemaker handles calculations with time/duration variables. Does anyone have suggestions regarding the easiest way to these variables in caculations (or scripts)? In particular, I would like to find a way to calculate something like the following:

3:10 * 5

(that is 3 hours and 10 minutes, times 5) to yield 15:50

or

(EndDate - StartDate)*(EndTime-StartTime)

to get a total duration of some daily activity for the specified time

Does anyone have a custom calculation or script that does something like this? Or perhaps does FileMaker have some functions I haven't found yet? (The only thing I've figured out so far is converting everything into minutes, doing the calculation, and then using remainder calculations, etc. to re-assemble a time value. There must be a better way!

[RalphL]

Time is seconds & date is days. A day = 24 Hours/day * 60 Minutes / Hour * 60 Seconds / Minute. You can choose to display the result as a number, a time or a date. The date may look a little strange.

[dd3]

Thank you, Ralph. I guess that should have been obvious to me. I'm still struggling, though, to get the results of a time calculation to display as a calculation. For example, I have function that calculates

2*(3:30 - 3:00) and comes up with 3600. I would like the 3600 seconds caluclated to appear as 1:00

(1 hour, zero minutes). The field (having the value 3600) is of type TIME. The format of the field on the layout is specified as "hh:mm". But it still shows up as "3600".

How do I make filemaker treat this calculated result like a time value, and show it to me in hh:mm form?

[Lee Smith]

Hi dd3, and welcome to the Forum.

If you are not concerned about weekends, this will give you the elapsed time:

((End Date - Start Date) * 86400) + Time Out - Time In

and by hard coding it with the * 5, it will provide you with your desired result:

(((End Date - Start Date) * 86400) + Time Out - Time In) *5

If the "5" represents a number that needs to be changed from time to time, then use a field in its place. I have created a number filed called "No_of_Employees" for the purpose of demonstration:

(((End Date - Start Date) * 86400) + Time Out - Time In) * No_of_Employees

HTH

Lee

[RalphL]

The fact that you are getting 2 * ( 3:30 - 3:00) to produce 3600 tells me the calculation is set to be a number. I just tried this in FMP7 and got 1:00.

[dd3]

Hmmm. Well the field is defined as a Time variable. And the field on the layout is formatted to display a Time value (hh:mm). The calculation itself actually mixes Date and Time fields in an IF statement. In oversimplified form: (Day2 - Day1) * (Time2 - Time1). So perhaps that makes the calculated value a "number" rather than a "time" in spite of the formatting.

OK, here's an approach that works, leveraging the knowledge that time is stored in seconds and the answer needs to be made into a time instead of a number -- just use the Time function.

Time(0,0,(Day2 - Day1) * (Time2 - Time1))

Whew, it works.

Thank you for all your thoughts and inspiration. I was getting ready to give up.