CompletionDateTime function

[staticbob]

Guys,

Somebody help me out with this logic, I'm struggling.

I have a ProcessStartDateTime.
I have a ProcessTime in minutes.
I have a WorkingTimeSetting that gives the hours to be worked.
I need to get the ProcessEndDateTime.

This didn't appear too difficult until I start thinking about weekends. I'm ignoring public holidays for now !

Here's what I have so far, not sure what its giving me just yet, still trying to work it out.

Private Function GetEndDate(worktime, startdate, ProcessTime) As Date
Dim HoursPerWeek, MinutesPerWeek, HoursPerDay, NumberOfWeeks, ExtraHours, NumberOfDays, enddate

ProcessTime = 5400 ' 2.25
'4800 = 2


HoursPerWeek = Right(Left(worktime, 3), 2)
MinutesPerWeek = HoursPerWeek * 60

HoursPerDay = HoursPerWeek / Left(Right(worktime, 5), 1)
NumberOfWeeks = ProcessTime / MinutesPerWeek

WholeWeeks = Round(NumberOfWeeks, 0)
ExtraHours = (NumberOfWeeks - WholeWeeks) * HoursPerWeek

ProcessHours = ProcessTime / 60
NumberOfDays = ExtraHours / HoursPerDay
enddate = DateAdd("ww", NumberOfWeeks, startdate) + NumberOfDays + (ExtraHours / 24)

'If DayOfWeek(enddate) = Saturday Then enddate = enddate + 2

GetEndDate = enddate

' worktime examples
'    cbo_worktimes.AddItem ("(40h) 8hr 5days")
'    cbo_worktimes.AddItem ("(48h) 8hr 6days")
'    cbo_worktimes.AddItem ("(72h) 12hr 6days")


End Function

[stanav]

I strongly suggest you to turn Option Explicit and Option Strict ON.
Now back to your question, what are the types of worktime, startdate and processtime as in this function declaration?

Private Function GetEndDate(worktime, startdate, ProcessTime) As Date

Also give us a short description of what the inputs (to your function) mean and what the output should be because I tried hard and still couldn't figure out what you want to achieve in your original post.

[staticbob]

Hi Stanav, thanks for the response.

Yes, Option strict and explicit will get turned on, but at the moment this will be VBS so I dont want to make too many changes that I will need to undo.

As far as the function inputs go...

worktime = a string, along the lines of "(40h) 8hr 5days". Examples at the bottom of op.
startdate is a datetime, example 25/05/08 10:00:00
Processtime is a double, example 40800

So, the startdate is the date the process will start.
Processtime is the number of working minutes the process will take to complete.
Worktime is the working hours/pattern that I need to calculate from. Similar to the default calendar functionality in MSProject.

So with the example data above, my output needs to give me a datetime that is 40800 WORKING minutes from the startdate, using the worktime to tell me when working minutes/hours are.

Does that explain better?

TIA,
Bob

[stanav]

OK... Thank you for a very detailed explanation of the problem. It is very clear now in my mind on how to get it solved.
1. Worktime: you somehow need to parse out the parts from the string. If the format is always the same as your example, this won't be too difficult. I'd use string.split(" "c) to separate the parts, then string.substring on each part to get the numeric values. The pieces you'll need to pulled from the worktime string are: TotalHoursWorkedPerWeek as Double, daysWorkedPerWeek. I don't think you'll need the hours worked per day since it's irrrelevant to the problem, and should you need it, you can always calculated from the other 2 piceices of info.

2. Total worked days needed to complete process = processTime / (totalhoursworkedperweek * 60)

3. Total weeks needed = total worked days / days worked per week

4. End date without counting holidays = startdate.AddDays(totalweekneeded * 7)

5. Count how many holidays you have from start date to end date (just calculated)... You need to have a list of holidays and check each one to see if it's in the range.

6. Final end date = calculated end date.AddDays(number of holidays in period)

If you have problem get this into code, let me know.