9 Replies Latest reply on Feb 25, 2010 2:03 PM by enyko

    Format Time of Day Field



      Format Time of Day Field


      Hey guys,


      I have created a text field for "Time of Day". I'd like to be able to simply enter "530" and it auto format to 5:30 PM. How do I do this? I'd like the default can always assume PM.



        • 1. Re: Format Time of Day Field
             Are you using FMP 10? If so I'd write a script to replace the entered text with a formatted version and set the script trigger to execute the script when the user exits the field.
          • 2. Re: Format Time of Day Field

            We are using FMP 10 but I am a bit lost on your description. Can you describe exactly what you mean with an example possibly?





            • 3. Re: Format Time of Day Field

              How about using an auto-enter on your time field of:


              Time ( Left ( YourTimeField ; 2 - ( Length ( YourTimeField ) = 3 ) ); Right ( YourTimeField ; 2 ) ; 0 )



              • 4. Re: Format Time of Day Field

                Got it working with your example Mr. Vodka, though since it knows there are 24 hours in a day, it always uses AM when I enter in a time. Can I have it automatically use PM because most of my times (if not all) will be PM. I don't want to have to enter 1430 for 2:30PM (which works). And in the few cases it is AM, I type in 1100a and it will know it's AM?


                Example, any number I enter in will assume PM (745 = 7:45 PM, 1105 = 11:05 PM...etc.). When I have an "a" after the number, it will know to use AM (745a = 7:45 AM, 1105a = 11:05 AM)


                Is this possible, or should I just train myself to use the 24 hour times?



                • 5. Re: Format Time of Day Field



                  Thank you for your post.


                  Yes, you can modify your auto-enter calculated value (or separate calculation field) to read:


                  Let (

                     [a = Trim (Time Of Day) ;

                     b = If ( Right ( a; 1 ) = "a" ; 1 ; 0 ) ; 

                     c = Filter ( a ; "0123456789" ) ] ;

                     Left ( c ; Length ( c ) - 2 ) & ":" & Right ( c ; 2 ) & " " & If ( b = 1 ; "AM" ; "PM" )






                  If a space is entered at the beginning or end of entry, the Trim function removes those spaces.  This information is temporarily stored in variable "a".

                  Next, the formula checks to see if "a" (for AM) is entered at the end of the entry.  Variable "b" is set to 1 for "AM" and 0 (zero) for "PM".

                  Next, the formula filters out only the numerical data and stored into variable "c".  (That is, remove "a" for AM, if it exists from the entry).

                  Finally, extract the hour portion, concatenate a colon, extract the right two numbers for minutes, add a space, and if an "a" was entered, add "AM".  Otherwise add "PM".


                  Let me know how this works for you.



                  FileMaker, Inc. 

                  • 6. Re: Format Time of Day Field

                    Hey TSGal,


                    Works lovely except for the part we will rarely use...the AM. How am I supposed to get the AM to show? When I type; 330a it errors.



                    • 7. Re: Format Time of Day Field

                      I dont believe her calc will work with a time field, although it would in a text field.


                      I think that you can simply do something like the following:


                      Auto-enter calc (Do not replace unchecked):



                      Case ( not $trigger;

                         Let ( [ n = Filter ( YourTimeField; "1234567890");
                                    s = SerialIncrement ( "0000"; n );
                                    t = Time ( Left ( s; 2 ) ; Right ( s ; 2 ); 0)
                                 ]; t + Case ( Hour ( t ) < 12; 43200 )
                              ); YourTimeField





                      Then you can use a script trigger of OnObjectKeyStroke:



                      Set Variable [ $tk; Value:Code ( Get ( TriggerKeystroke ) ) ]
                      If [ $tk = 29 or $tk = 31 ]
                         Set Variable [ $trigger; Value:1 ]
                         If [ Hour ( YourTimeField ) < 12 ]
                             Set Field [ YourTimeField; YourTimeField + 43200 ]
                         Else If [ Hour ( YourTimeField ) ≥ 12 ]
                             Set Field [ YourTimeField; YourTimeField - 43200 ]
                         End If
                         Exit Script [ Result: True ]
                      End If



                      This will allow you to use the up and down arrow keys to toggle between AM and PM.

                      Make sure that your Time is formatted to display as AM and PM. (Click the time field and select Time format from Format Menu )





                      *Edit: BTW you probably will want to add some validation checks in for a valid date, empty fields, etc...


                      • 8. Re: Format Time of Day Field



                        The answer from mr_vodka is correct (Thank you!).  My calculation will not work in a Time field; only in a text field.  The solution provided by mr_vodka may be your best bet.  Another possibility would be to enter the information into a text field, and then store the information in a time calculation field.



                        FileMaker, Inc. 

                        • 9. Re: Format Time of Day Field

                          Hey guys,


                          This is an old question but I had a follow up. This question will also pertain to many fields.


                          So if you re-read this thread, it said that TSGal's script works great but must be a text field and not a time field. Well, we've left this formula in a time field and never changed it to a text field. But now want to. I've tested it and it works great to incorporate the "AM". My question is, if we change this existing field to now be a text field, how does this effect all my existing data in this field?


                          As you can imaging, I don't want this change to change any existing fields. This is a general question overall in FMP because I sometimes want to make a change to a field but want to retain all the existing data for all records in that field. Is this achieved with the "Validate data in this field - Only during data entry"?