7 Replies Latest reply on Jul 19, 2012 11:48 AM by JamesSanders

    Financial Calculations based on time

    JamesSanders

      Title

      Financial Calculations based on time

      Post

      Hi, I am fairly new to FileMaker 10. I am having an issue with a calculation involving time and the cost per amount of time. I am looking for help in finding a formula that will allow a field called "Total Cost" to get the total time (in increments of 15 min.) and then add the $ amount based on the total time (  i.e., if the total time is 15 min. then the cost would be $10.00, if the total time is 30 min. then the cost would amount to $20.00). I have been beating my head on my desk for awhile about this one, so, any input would be most appreciated. Thanks, James.

        • 1. Re: Financial Calculations based on time
          schamblee

          The calculation for field totalcost would be (time/15)*10.    You didn't state how you handle the amount if it was odd time such as 16 minutes.   If you round up to the whole 15 minutes then you would use ceiling(time/15)*10.

          • 2. Re: Financial Calculations based on time
            philmodjunk

            And where do you put the $10 for every 15 minutes data? Will this be the same rate in every calculation or will other data specify a different rate for different records in the table?

            And what time data do you have recorded and where?

            Say you have these fields in the same table:

            TimeStart
            TimeEnd
            Rate
            Cost

            Your calculation might be:

            Round ( Rate * (TimeEnd - TimeStart ) / ( 15 * 60 ) ; 2 ) //15 minutes times 60 seconds per minute

            or

            Round ( Rate * (TimeEnd - TimeStart ) / 900 ; 2 )

            TimeEnd and TimeStart are best defined as time stamp fields so that the calculation remains correct even if TimeStart is before midnight and TimeEnd falls after midnight. If it's never the case that the elapsed time will be more than 24 hours and will never cross the midnight hour, you can use fields of type time instead of TimeStamp.

            Subtracting one Time or TimeStampe field from another Time or TimeStamp field computes the elapsed time in seconds.

             

            • 3. Re: Financial Calculations based on time
              JamesSanders

              I have a field called "Total Time" that works correctly, my issue is that I want the field "Total Cost" to calculate the total time (in blocks of 15 min.) and then apply the total. So, if the total time is 0-14 min., the total cost would be $0.00, if the total time is between 15-29 min., the cost would be $10.00, if the total time is 30-44 min., the cost is then $20.00. I will try some of the tips that have been suggested so far and see if that works. Thanks for the input so far.

              • 4. Re: Financial Calculations based on time
                philmodjunk

                If Total Time is a a time field or returns total time in seconds:

                Floor ( TotalTIme * Rate / 900 )

                will compute the total cost, discarding any fractional amount less than a 15 minute interval.

                • 5. Re: Financial Calculations based on time
                  JamesSanders

                  Thanks Phil, that pretty much did the trick! Any tips on how to get FileMaker to produce dollars signs ($)? I added "" and () around the  $10.00 part of the equation, but it only produces numbers. Thanks for all your help.

                  • 6. Re: Financial Calculations based on time
                    philmodjunk

                    I'd use data formatting in the inspector to format this field as currency. Then the $ and a specified number of decimal places can be used to format the value of this field.

                    • 7. Re: Financial Calculations based on time
                      JamesSanders

                      Cool, that works!! Thanks alot.