Duration Algorithm

  1. 6 months ago

    Dave S

    14 Mar 2019 San Diego, California USA

    I am working on an app that tracks tasks...
    each task has 3 attributes

    • Start Date
    • End Date (planned)
    • Estimated Duration

    The user will enter TWO of those three and the third will be calculated
    The problem is for calculating either Start or End date, the duration is specified in "working days", so it is not a simple matter of just adding or subtracting "days" from a "date". The time span may include holidays, not to mention the working days of the week may vary

    For example

    Start Date : 22-Dec-2023
    End Date : 27-Dec-2023
    the duration is 3 days not 6, since the 23,24 are weekend, and Monday the 25th is a holiday

    Any ideas how to do this quickly and accuratly?

  2. Derk J

    14 Mar 2019 Pre-Release Testers, Xojo Pro

    Xojo.core.dateinterval

  3. Dave S

    14 Mar 2019 San Diego, California USA

    @Derk J Xojo.core.dateinterval

    No..... as that does not take into account holidays or non-working days... all of which I explained were requirements

    @Dave S so it is not a simple matter of just adding or subtracting "days" from a "date".

  4. Beatrix W

    14 Mar 2019 Pre-Release Testers, Third Party Store Europe (Germany)

    Xojo.core.Dateninterval won't help. I did a Goggle and found https://stackoverflow.com/questions/1617049/calculate-the-number-of-business-days-between-two-dates .

  5. Edited 6 months ago

    But in the USA, there is no difference between a day and a working day, isn't it? (pun intended) :-)

  6. Dave S

    14 Mar 2019 San Diego, California USA

    @Beatrix W Xojo.core.Dateninterval won't help. I did a Goggle and found https://stackoverflow.com/questions/1617049/calculate-the-number-of-business-days-between-two-dates .

    was hoping for something quicker. but that might do... for ONE of the scenarios (calculate duration given start and end)

    but not for the other two

  7. @Dave S — Personally, I would implement a recursive method going backwards/forwards in time by 24h step, determine if one particular day is considered a working day or not, and continue until the task had enough time to be fulfilled.

  8. Dave S

    14 Mar 2019 San Diego, California USA

    @Stéphane ;Mons @Dave S — Personally, I would implement a recursive method going backwards/forwards in time by 24h step, determine if one particular day is considered a working day or not, and continue until the task had enough time to be fulfilled.

    again I was hoping for less than brute force, since there are hundreds of these that need to be calculated.

  9. @Dave S — Well, it is not elegant but it would do the job easily and quickly because there is nothing math-intensive. And when I said "recursive", it could actually be implemented in a loop, so it is not really recursive.

  10. Kevin G

    14 Mar 2019 Pre-Release Testers, Xojo Pro Gatesheed, England

    One way would be to convert all dates to a number of days from an epoch. This would allow you to determine the interval between the two dates and test to see if non working days were in range.

    Here is a very basic idea I knocked up in about 10 minutes.

    Const kSecondsInADay = 86400
      
      Dim duration As Int32
      Dim s, e As Date
      Dim sInDays, eInDays As Int32
      Dim holidayList(-1) As Date
      Dim nwStartDate, nwEndDate As Date
      Dim holidayDate As Date
      Dim holidayCount, i As Int32
      Dim hInDays As Int32
      
      duration = 0
      
      'start & end dates
      s = New Date(2023,12, 22)
      s.Hour = 0
      s.Minute = 0
      s.Second = 0
      
      e = New Date(2023,12, 27)
      e.Hour = 0
      e.Minute = 0
      e.Second = 0
      
      
      'build some fake data
      
      'build a list of public holidays
      holidayList.Append(New Date(2023, 12, 25))
      
      
      'find the first non working day (saturday or sunday) within the start and end dates
      nwStartDate = New Date(s)
      nwEndDate = New Date(e)
      
      If (nwStartDate.DayOfWeek > 1) And (nwStartDate.DayOfWeek < 7) Then
        nwStartDate.Day = nwStartDate.Day + (7 - nwStartDate.DayOfWeek)
      End If
      
      'add the non working days to the holiday list
      While nwStartDate <= nwEndDate
        'add the first non working day
        holidayDate = New Date(nwStartDate)
        
        If holidayList.IndexOf(holidayDate) = -1 Then
          holidayList.Append(holidayDate)
        End If
        
        
        'add the second non working day
        nwStartDate.Day = nwStartDate.Day + 1
        
        holidayDate = New Date(nwStartDate)
        
        If holidayList.IndexOf(holidayDate) = -1 Then
          holidayList.Append(holidayDate)
        End If
        
        
        'skip to the following week
        nwStartDate.Day = nwStartDate.Day + 6
      Wend
       
      
      
      'determine the duration
      
      'calculate the start and end time into the number of days from our date epoch
      sInDays = Floor(s.TotalSeconds / kSecondsInADay)
      eInDays = Floor(e.TotalSeconds / kSecondsInADay)
      duration = (eInDays - sInDays) + 1
      
      
      'subtract the holidays
      holidayCount = UBound(holidayList)
      For i = 0 To holidayCount
        hInDays = Floor(holidayList(i).TotalSeconds / kSecondsInADay)
        
        If (hInDays >= sInDays) And (hInDays <= eInDays) Then
          duration = duration - 1
        End If
      Next
      
      
      
      break

or Sign Up to reply!