Duration Algorithm

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?


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

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 .

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

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

@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.

@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.

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.

[code]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
End If

'add the second non working day
nwStartDate.Day = nwStartDate.Day + 1

holidayDate = New Date(nwStartDate)

If holidayList.IndexOf(holidayDate) = -1 Then
End If

'skip to the following week
nwStartDate.Day = nwStartDate.Day + 6


'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