9 Replies Latest reply on Feb 29, 2016 1:55 PM by user19752

    Date and Time Calculation Question

    pbedouk

      Hi

       

      I am trying to create calendar entries for various events. 

       

      Some events cross midnight boundaries and some don't, depending on duration and starting times.

       

      With a known Starting  Date (StartDate), starting Time of Day (StartToD) and Duration in hours (DurationHours), how can I calculate the End Date (EndDate) and ending Time of Day (EndToD)?

       

      eg:  Start date and time = 2016-01-01 at 15:00hrs.

      if the event duration is 20 hours, what is the end date and what is the ending time of day?

      and if the duration is 3 hours, what is the end date and what is the ending time of day?

       

      Many thanks in advance!

       

      Peter.

        • 1. Re: Date and Time Calculation Question
          planteg

          Hi Peter,

           

          have a look at time and dates functions. You can for example subtract and add dates of times.For your application, you should use a TimeStamp which is a date and a time. Now regarding the duration of an event, do the 20 hours include night(s) ? If not, you should then take into account part of the day where the event is not taking place.

           

          On way to play around with functions is the Data Viewer in File Maker Pro advanced, for example:

          timestamp.jpg

          Timestamp() creates the time stamp for the start of the event. To that we add the number of seconds in 20 hours (a timestamp is in seconds) and we get a new timestamp 20 hours later.

          • 2. Re: Date and Time Calculation Question
            siplus

            EndToD = let (

            l = time(DurationHours;0;0);

            time(mod(hour( StartToD + l ) ; 24) ; minute(StartToD); seconds(StartToD))

            )

             

            EndDate = StartDate + (EndToD < StartToD)

            • 3. Re: Date and Time Calculation Question
              beverly

              Yes, timestamp can do the math. You can also have fields that auto-enter date and time (mostly for display and other options) into two fields based on the timestamp.

               

              I have date_start, time_start, date_end, time_end in an "event" database I manage for my own usage. All of these can be used to do "the math"! The timestamp just combines both and works well, too.

              beverly

              • 4. Re: Date and Time Calculation Question
                user19752

                In addition, you can use Time(20;0;0) function at duration.

                 

                OP already have these as proper type of fields, so

                Timestamp ( StartDate ; StartToD ) + Time ( DurationHours ; 0 ; 0 )

                get result as timestamp.

                If want indivisual fields for date and time, there are functions.

                GetAsDate ( timestamp )

                GetAsTime ( timestamp )

                • 5. Re: Date and Time Calculation Question
                  pbedouk

                  Thanks for this ... It seems to be the closest to what I am trying to do, but I can't get it to work ... my skills are not up to that level to be able to debug.

                   

                  should EndDate = StartDate + (EndToD < StartToD) have a plus sign?  though that doesn't work for me either.

                   

                  I've attached some screenshots of what I've tried.

                   

                  Thanks.

                   

                  Layout.png

                  Script.png

                   

                  Let Statement.png

                   

                  Data Viewer.png

                   

                  Peter

                  • 6. Re: Date and Time Calculation Question
                    user19752

                    There are many ways, but as planteg said, in this problem you can use timestamp to make things easy.

                     

                    Set Variable [ $endTimestamp ; Value:Timestamp ( Globals::gStartDate ; Globals::gStartToD ) + Time ( Globals::gDuration ; 0 ; 0 ) ]

                    Set Field [ Globals::gEndToD; Value:$endTimestamp ]

                    Set Field [ Globals::gEndDate; Value:$endTimestamp ]

                     

                    This calculate correct value for any of duration, not only crossing one midnight.

                    • 7. Re: Date and Time Calculation Question
                      pbedouk

                      Thanks to everyone for their help. Timestamps are the way to go for this question.

                       

                      here are the details which worked for me:

                       

                      Script:

                      Script.png

                      Fields: (remember to set the duration as a number, not as a time. Decimals work too (eg 12.5))

                      Fields.png

                       

                      My testing layout:

                       

                      Layout.png

                      Thanks again to everyone.

                       

                      Peter.

                      • 8. Re: Date and Time Calculation Question
                        pbedouk

                        Thanks .. needed to add GetAsDate and GetAsTime.

                         

                        worked perfectly.

                        • 9. Re: Date and Time Calculation Question
                          user19752

                          "Set Field" converts the result to its field type, then usually can omit GetAs... function. I tested it before post. But using them always may be safe and good to readability for someones (I like shorter version always)