9 Replies Latest reply on Oct 23, 2009 10:06 AM by Rayban

    Displaying a calculated time field with 'Hour's and 'minutes'

    Rayban

      Title

      Displaying a calculated time field with 'Hour's and 'minutes'

      Post

      I'm working in Filemaker 10 on a simple timesheet. I've got a calculated field which adds up the sub totals of line time entries (i.e. someone enters a start and a stop time for various tasks during the day - a calculation works out the time for each event and then the field in question adds all these sub totals up).

       

      It's all working fine except it's showing it as e.g. 25:20 and I would like it to display as a more user friendly '25 hours 20 minutes'.

       

      I tried using the help on the Http://filemaker.custhelp.com page 'Elapsed Time Calculation Sampler'

       

      TimeAsText1b (Calculation, Text result) = 

      Hour (ElapsedRawTime) & (If (Hour (ElapsedRawTime) = 1, " hour, ", " hours, ")) & Minute (ElapsedRawTime) & (If (Minute (ElapsedRawTime) = 1, " minute ", " minutes "))

       

      I've deleted the first line and swapped out 'ElapsedRawTime' for my field but it keeps returning an error message at the comma after the first "1" - I realise the above help page is for older versions of FM. Any suggestions what I'm doing wrong with this? 

       

      Many thanks 

       

        • 1. Re: Displaying a calculated time field with 'Hour's and 'minutes'
          philmodjunk
            

          When you open up your specify calculation dialog for this field is this Exactly what you entered?

           

          Hour (ElapsedRawTime) & (If (Hour (ElapsedRawTime) = 1, " hour, ", " hours, ")) & Minute (ElapsedRawTime) & (If (Minute (ElapsedRawTime) = 1, " minute ", " minutes "))

           

          I pasted that into a sample database, changing ElapsedRawTime to a field named in my DB's table and it didn't trigger any error message.

           

          Using FMP 10.03 advanced.

          • 2. Re: Displaying a calculated time field with 'Hour's and 'minutes'
            Rayban
              

            Yes that's what I've pasted in, swapping out the ElapsedRawTime for the new field name.

             

            What I've done, so as not to mess up the working calculation field (let's call it 'CurrentTimeTotal'), was create a new field (TimeInHrsMins) with the calculation dialog changing 'ElapsedRawTime' to 'CurrentTimeTotal'.

             

            What I get is a message, before I can save the dialog, saying  "A number, text constant, field name or "(" is expected here." The cursor then goes to the comma after the first "1". 

             

            I'm on 10.0v3 too. 

            • 3. Re: Displaying a calculated time field with 'Hour's and 'minutes'
              ninja
                

              Howdy,

               

              A stupid question I can't believe I'm even typing...but shouldn't the commas be semicolons?  Perhaps it doesn't matter...they are default to semicolons in FMP8.

               

              Just caught my eye...

              • 4. Re: Displaying a calculated time field with 'Hour's and 'minutes'
                philmodjunk
                  

                Nope, Filemaker simply substitutes ; for , after you click OK.

                 

                Since I can copy and paste the text directly into filemaker and not get an error, that suggests something else is wrong. Exactly what error message do you get when you paste this text into the specify calculation dialog and click OK? Any part of the expression then get highlighted?

                • 5. Re: Displaying a calculated time field with 'Hour's and 'minutes'
                  Rayban
                    

                  When I try to save the dialog script for that field the message that comes up is

                   

                  "A number, text constant, field name or "(" is expected here."

                   

                  It then puts the cursor to the comma after the first "1".  I've tried to get the script to work on another time field and it's the same thing. I take it that the field I'm referring the script to should be a calculation field with a Time result and that I display the results in the new field (using the discussed calculation script) as a text result?   

                   

                  Sounds like it's working perfectly for you Phil. 

                  • 6. Re: Displaying a calculated time field with 'Hour's and 'minutes'
                    philmodjunk
                      

                    Apologies for making you post your error message twice. I missed that item which I now spot in an earlier post by you.

                     

                    That error message would seem to indicate that you are missing a right parenthesis somewhere to the left of the "1". If you still can't spot the cause of this error message, copy and paste the entire contents of your expression to this thread and maybe we can spot the problem.

                    • 7. Re: Displaying a calculated time field with 'Hour's and 'minutes'
                      Rayban
                        

                      Here's what I'm putting in - where the field 'Current Time Total' is a calculation field  (with a time result) carrying the formula    Sum (Line Items CGD Timesheet::Line Time Total)

                       

                       

                      I'm trying to make a new field that looks at Current Time Total and shows the time as e.g. 23 hours 30 minutes with the formula below 

                       

                      Hour (Current Time Total) & (If (Hour (Current Time Total) = 1, " hour, ", " hours, ")) & Minute (Current Time Total) & (If (Minute (Current Time Total) = 1, " minute ", " minutes "))

                       

                       

                      I'm struggling to get to grips with calculations so I'm sure there's probably a more elegant way of incorporating the formatting I'd like into the 'Current Time Total' field together with the Sum calculation but I'm not up to that yet.

                       

                      Thanks for your help. 

                      • 8. Re: Displaying a calculated time field with 'Hour's and 'minutes'
                        philmodjunk
                          

                        How truly weird.

                         

                        I've been looking at your expression for several minutes and don't see any syntax errors. I copy your expression and paste it into an FMP 10.03 calculation field dialog, change the field name to that of a time field in the same table and click OK--no error messages. Field is accepted as having correct syntax.

                         

                        You might try re-entering the expression from scratch, typing it in by hand.

                         

                        You might try this simplified expression:

                         

                        Hour (Current Time Total) & " hours, " & Minute (Current Time Total) & " minutes "

                         

                        (The extra if functions just remove the "s" if the number hours or minutes is singular and you can probably live without that extra wrinkle.)

                        • 9. Re: Displaying a calculated time field with 'Hour's and 'minutes'
                          Rayban
                            

                          It is very strange. But it's good to hear it's maybe not me at fault - I was beginning to doubt my sanity and what I was going.

                           

                          I tried re-typing it from scratch but no joy.

                           

                          However it's very happy with your simplified expression - went in first time with no bother! Many thanks for coming up with that and I'm happy to take this workaround - a weird glitch though...

                           

                          Thanks again

                           

                          Rayban