5 Replies Latest reply on Apr 10, 2012 11:38 AM by ntay

    Elapsed seconds between timestamps (excluding weekends)

    ntay

      Title

      Elapsed seconds between timestamps (excluding weekends)

      Post

      Does anyone know how to calculate the number of seconds between two timestamps excluding weekends?

        • 1. Re: Elapsed seconds between timestamps (excluding weekends)
          philmodjunk

          Only weekends? No three day weekends due to holidays' etc?

          There are calculations for the number of days between dates that could be adapted to subtract out 48 hour's worth of seconds for every weekend, once can be found in the KnowledgeBase, but the expression assumes that every weekend and only weekends be omitted and this often does not match the real world. There are other approaches that work from a calendar of weekends and holidays that can flexibly omit not only weekends but any holiday dates that also fall on the time interval in question.

          • 2. Re: Elapsed seconds between timestamps (excluding weekends)
            ntay

            I am interested in both approaches.  The latter would be ideal. 

            • 3. Re: Elapsed seconds between timestamps (excluding weekends)
              philmodjunk

              They are very different approaches.

              Here's the knowledge base article on counting week days: http://help.filemaker.com/app/answers/detail/a_id/5281/kw/work%20day

              I'm leaving it up to you to convert days into seconds, but this should be possible.

              Accounting for holidays requires a table of either holiday dates, dates you are not on holiday or both with a status field to identify which are which. Then you can deduct 24 hours worth of seconds for each holiday that falls in the interval between your two time stamps.

              with both approaches, you may need to use GetAsDate ( TimeStampfield ) to extract just the dates in question in order to allow for holidays and week ends.

              • 4. Re: Elapsed seconds between timestamps (excluding weekends)
                ntay

                Thanks.  I think that's enough to get me started.

                • 5. Re: Elapsed seconds between timestamps (excluding weekends)
                  ntay

                  Here's what I ended up doing.  Since I wanted to store the result anyway, I used a script instead of calculation.  I found that I needed to adjust the formula from the knowledge base by subtracting 1.  If the end date was immediately succeeding the start date, the number of days between them needed to be zero instead of 1 since I was already accounting for that time by counting seconds included in the first and last days.  The script stores the result in Table1::TotalSeconds.

                   

                  Before running the script, I just need to set 2 variables:  $$StartTimestamp and $$EndTimestamp

                  • Allow User Abort [ Off ]
                  • Set Error Capture [ On ]
                  • # If started and finished on the same day, just subtract
                  • If [ GetAsDate($$StartTimestamp) = GetAsDate($$EndTimestamp) ]
                  • Set Field [ Table1::TotalSeconds; GetAsNumber(GetAsTimestamp($$EndTimestamp) - GetAsTimestamp($$StartTimestamp)) ]
                  • Exit Script [ ]
                  • End If
                  • # Count the seconds included in the last day
                  • Set Variable [ $LastDaySeconds; Value:GetAsNumber( GetAsTimestamp($$EndTimestamp) - Timestamp(GetAsDate($$EndTimestamp) ; Time(0;0;0) ) ) ]
                  • # Count the seconds included in the first day
                  • Set Variable [ $FirstDaySeconds; Value:GetAsNumber( Timestamp(GetAsDate($$StartTimestamp) ; Time(23;59;59)) - GetAsTimestamp($$StartTimestamp) ) ]
                  • # Count the number of days between dates, excluding weekends
                  • Set Variable [ $WeekdaysBetween; Value:5 * Int ( (GetAsDate($$EndTimestamp) - GetAsDate($$StartTimestamp) ) / 7 ) +Middle ( "0012345501234544012343340123223401111234010012340" ; 7 * (DayOfWeek ( GetAsDate($$StartTimestamp) ) - 1 ) + DayOfWeek ( GetAsDate($$EndTimestamp) ) ; 1 ) - 1 ]
                  • # Count the number of holidays between dates
                  • Go to Layout [ “Holidays” (Holidays) ]
                  • Enter Find Mode [ ]
                  • Set Field [ Holidays::Date; GetAsDate($$StartTimestamp) & "..." & GetAsDate($$EndTimestamp) ]
                  • Perform Find [ ]
                  • If [ Get(LastError) = 401 ]
                  • Set Variable [ $Holidays; Value:0 ]
                  • Else
                  • Set Variable [ $Holidays; Value:Get(FoundCount) ]
                  • End If
                  • Go to Layout [ original layout ]
                  • # Convert number of days between dates to seconds
                  • Set Variable [ $WeekdaySeconds; Value:$WeekdaysBetween * 86400 ]
                  • # Convert holidays to seconds
                  • Set Variable [ $HolidaySeconds; Value:$Holidays * 86400 ]
                  • # Calculate total seconds
                  • Set Field [ Table1::TotalSeconds; // Seconds included on First Day + Seconds included on Last Day + seconds of all days between - Holiday Seconds $FirstDaySeconds + $LastDaySeconds + $WeekdaySeconds - $HolidaySeconds ]