5 Replies Latest reply on Apr 23, 2012 4:03 PM by philmodjunk

    Adding and Subtracting Time

    JasonKrueger

      Title

      Adding and Subtracting Time

      Post

      I am completely new to FMP, but have quite a bit of experience with Excel and Numbers, so am not complete new to writting formulas. 

      I am trying to create a simple Timeclock. I have created four fields which will gather Current Time, when a coordsponding button is pressed:

      Clock in, Lunch in, Lunch out, Clock out. 

      I have created another field which will acumulate the total time worked in a day. I have been playing around with how to get this field's equations to funciton properly. 

      I have read through many of the other forum posts on how to make this work, and have spent a couple of hours reading through the functions refference, and cannot seem to make them function properly. All of the results it is yielding make no sense.

      I have tried: MOD ( Lunch in - Clock in + 86400 , 86400) as suggested on this forum:

      http://help.filemaker.com/app/answers/detail/a_id/4037/kw/TIME%20subtract/session/L3RpbWUvMTMzNDk2NTI4Ny9zaWQvTjZOYVI4V2s%3D

      I'm at a loss, this seems like it should not be difficult. 

      Thank you for your help

        • 1. Re: Adding and Subtracting Time
          philmodjunk

          Lunch out - Clock in + Clock Out - Lunch in

          Will compute the total elapsed time in seconds.

          ( Lunch out - Clock in + Clock Out - Lunch in ) / 3600

          will convert seconds into hours. (Instead of dividing by 3600, you can also choose Time as the result type and format the field to display HH::MM::SS)

          Use TimeStamp fields instead of time fields if there is any chance that this interval might cross the midnight hour.

          • 2. Re: Adding and Subtracting Time
            JasonKrueger
            Phil
            Thank you for the quick response. I am still getting nonsensical output from these equations, and I'm not sure why. To save time I have some screen shots to show you what I see. 
            There is a screen shot of the formula, as entered, and then a series of shots as I push the various time buttons. 
             
            • 3. Re: Adding and Subtracting Time
              philmodjunk

              Calculation result should be number, but that's not the cause of the incorrect result.

              Are the four data field of type time or are they text?

              When I type in these 4 time values, I get a result of .0061111111111111

              Since I get the same result you do when I just enter IN and Go to Lunch figures, (.0033333333333333) I'd look at the data types for the other two fields here and also make sure that the fields placed on the layout are the ones used in the calculation.

              • 4. Re: Adding and Subtracting Time
                JasonKrueger

                Ok, figured a couple of things out.

                The field does not update until it, (or something else is) selected. In other words when I press the Clock Out button, and it populates in the value, it does not update the Total Hours value until you click on anything else. This effect was causing each of the calculations in the screen shots, to be behind one calculation set. 

                I am sure I can resolve this with a script on the buttons, and possibly programing in the calculations at that level, so that when you clock out for lunch it updates correctly to show total hours worked prior to lunch, and then when you clock out at the end of the day it updates to include all hours worked. 

                All of the crazy data on the in between was the times getting divided by 3600, after being subtracted from 0, basically allowing every other calculation to make no sense. If I do decide to leave it as a calculation embedded in the Total Hours field then I can use an IF statement to make sure it does not do this. 

                All of the fields in the calculations were on the layout, however they where not all set to time, I changed them all to time.

                Is there a simple way to force the field to update after each field input occurs. 

                Thanks for your help. Sometimes it's hard to see the forest for the trees. 

                • 5. Re: Adding and Subtracting Time
                  philmodjunk

                  It updates for me each time I exit a field by tabbing into the next field. It stays up to date, but treats empty fields as zero so I get negative values whenever a person clocks in and hasn't yet clocked back out...

                  You could modify this calculation to be:

                  ( If ( Lunch out ; Lunch out - Clock in ) + If ( Clock Out ; Clock Out - Lunch in ) ) / 3600

                  Then you won't get negative values.

                  And you may want to consider using two records with an in and out date stamp field in place of one record with these four fields. That allows more easily for situations where a person works for part of a day and then leaves instead of taking a lunch break. A Summary field can total up the hours for any given day in order to get a daily total.

                  Using date stamps in place of time insures correct results even when some one works past midnight.