14 Replies Latest reply on May 27, 2017 12:21 PM by TomHays

    Adding a number to a time variable to produce a new time

    yomango

      Hello. A quick question: I need to convert pm time to military time. For instance, I have a 12:30pm, want to add 5 hours to produce a 17:30 or I have a 5:30 and need the outcome to show 17:30. Some fine soul gave me a script that ALMOST works, except for PM. It works with full hours, no minutes. 1pm, get a 13:00 and so on. But 6:30pm gets a 617:00. One more note, if the time variable shows 6.30 the result is 18.3:00, but I haven't been able to substitute the resulting period(.)  with the colon(:) sign. The calculation for pm looks similar to this: conv= (Case... pm=1; num+12&:00) where "num" is the variable time itself, without the "pm" text.

        • 1. Re: Adding a number to a time variable to produce a new time
          philmodjunk

          Time is time. 1pm and 13:00 are the same value internally. The only difference is the format used to display it. Thus, you can just take the field and specify a 24 hour format in the inspector.

          • 2. Re: Adding a number to a time variable to produce a new time
            DrewTenenholz

            yomango --

             

            ...and the fundamental unit of time in FileMaker is seconds.

             

            Given a field of type 'Time' called YourTime, here are two ways to add five hours:

             

            The quick way:

            YourTime + ( 5 * 60 * 60 )   sometimes people like to type YourTime + ( 5 * 3600 )

             

            The painfully explicit way:

             

            Let ( [

            begin= YourTime

            ; theHour= Hour ( begin )

            ; theMinute= Minute ( begin )

            ; theSeconds ( Seconds ( begin )

             

            ; newTime= Time ( theHour + 5  ; theMinute ; theSeconds )

            ];

            newTime

            )

             

            And PhilModJunk is right, we usually let the field settings on the layout determine the display as AM/PM or 24-hour time.

             

             

            If you are trying to treat time like text and manipulate it that way, you almost always go wrong.  The time/date engine in FileMaker is your friend, recalculating all the things you need it to: roll over past midnight, into the next day, the next month, the next year, etc. 

             

            If you are trying to put the time into a text string like an email, then GetAsText (YourTime ) will be a quick way to get something useful, but can vary depending on the system settings of the machine calculating it.  If you want a consistent format, you often end up writing a long calculation (or using a custom function) to get a reliable format no matter which client machine is doing the work.

             

            Sorry for the long-winded reply.

             

            -- Drew T.

            • 3. Re: Adding a number to a time variable to produce a new time
              user19752

              I surprised that there is no way to use GetAsTime() function, if the file is not created on AM/PM system format.

              How does it work on data viewer without opening file? It looks not respect current setting of OS.

              • 4. Re: Adding a number to a time variable to produce a new time
                yomango

                Time is in a variable so it has no format once it gets in the variable. At least I do not know how.

                • 5. Re: Adding a number to a time variable to produce a new time
                  user19752

                  If you have value "12:30pm" in variable, it is text, not time.

                  (So I tried to parse it using GetAsTime() but didn't work on my PC with time formatting 24h)

                   

                  If you set variable using Time(12;30;0), it is time.

                  • 6. Re: Adding a number to a time variable to produce a new time
                    yomango

                    Thank for taking the time to expand. I might not have explained myself right. I have a text showing several times (for medicament administration ie, (8am, 12N, 3:15pm, 9pm) To schedule the med administration I parse all the hours within the parenthesis by using a variable in a script, but I want 3:15pm to become 15:15, and as I posted, the script can convert all the ours to military hour, except the pm hours that have minutes, like 1:15 or 4:45, etc. How do I do it?

                    • 7. Re: Adding a number to a time variable to produce a new time
                      yomango

                      User19752. Thanks. Can't remember if I had try your suggestion since the script has to work for all AM, Noon and PM hours. Let me ask you, if you have a variable showing 6:50, how do you go about so the final variable result shows 18:50 instead.

                      • 8. Re: Adding a number to a time variable to produce a new time
                        user19752

                        An example

                        Evaluate("GetAsTime(\""&substitute(Lower($timestr);["n";"\")"];["am";"\")"];["pm";"\")+Time(12;0;0)"]))

                        not readable and tested so many, but doing simple, remove "N" and "am", add 12hour for "pm"...

                        • 9. Re: Adding a number to a time variable to produce a new time
                          user19752

                          Hmm, I don't use AM/PM but isn't 12:30pm 12:30 in military (12:30am is 00:30) ? If so my formula is not correct. It would need Case() function

                          • 10. Re: Adding a number to a time variable to produce a new time
                            yomango

                            Case() is what I am using but when the pm hour has minutes it does not convert. That way 9:05pm instead of being 21:05 it results on 917:00 By the calculation:9:05+12&";00"

                            • 11. Re: Adding a number to a time variable to produce a new time
                              user19752

                              I'm not sure about "N" (maybe only just 12:00)

                               

                              Let([

                              $ts="11:30pm"; //use your variable

                              am=Position ( $ts ; "am" ; 1 ; 1 ) ;

                              tm=GetAsTime ( Filter ( $ts ; "1234567890:" ) );

                              tm=tm-Case(tm>=Time(12;0;0);Time(12;0;0))

                              ];

                                tm + Case ( not am ; Time ( 12;0;0 ) )

                              )

                               

                              Hour in AM/PM format is 1..12, so subtract 12 hour if 12.

                              Then 12N become 00:00, add 12hours for N and pm

                              • 12. Re: Adding a number to a time variable to produce a new time
                                beverly

                                "12N" is noon or 12:00:00 (in military time, too) or 12:00:00 PM

                                 

                                12:00 AM is midnight or 00:00:00 in military time

                                Beverly

                                • 13. Re: Adding a number to a time variable to produce a new time
                                  philmodjunk

                                  Whether in a field or a variable time is still stored as the number of seconds from midnight to the time displayed in the field or merge variable. As long as you assign a value of type time to the variable and do nothing to the value that would convert it to text, you can put it on a layout as a merge variable and format the text object with time formatting. See these two images:

                                  TimeVariablesBrowse.png

                                  TimeVariablesLayout.png

                                  • 14. Re: Adding a number to a time variable to produce a new time
                                    TomHays

                                    It sounds like you have a user-entered value specifying the times of day in a text field.  The user's entry might look like (8am, 12N, 3:15pm, 9pm) which you are parsing out into individual times.

                                    Since users like to be creative, you might also encounter things like

                                    midnight

                                    noon

                                    or even times already in military time

                                    21:00

                                    Or you might have someone being lazy and just writing

                                    7a, N, 6p, 12m

                                     

                                    The following function will deal with any of that and return always hrs:min in military time with hrs and min being two digits.

                                     

                                    Let(

                                    [

                                    yourTimeString = "8 p";

                                    isPM = Position(yourTimeString; "p"; 1; 1);

                                    // will assume AM or already military time if no PM specified

                                    timeDigitsList = Substitute(Filter(yourTimeString; "1234567890:"); ":"; "¶");

                                    hrs = Right("00" & (GetValue(timeDigitsList;1) + If(isPM; 12; 0)); 2);

                                    min = Right("00" & GetValue(timeDigitsList;2); 2)

                                    ];

                                    Case(

                                       Position(yourTimeString; "mid"; 1; 1); "00:00"; // allows "mid" or "midnight"

                                       Trim(yourTimeString) = "m"; "00:00"; // just "m" with no number

                                       Position(yourTimeString; "12m"; 1; 1)> 0; "00:00";

                                       Position(yourTimeString; "12 m"; 1; 1); "00:00";

                                       Position(yourTimeString; "N";1; 1); "12:00";  // No reason to have "N" except when specifying noon.

                                       hrs & ":" & min

                                    )

                                    )

                                     

                                     

                                    -Tom