1. ## Re: task duration cacluation (all)

The start date/time + elapsed time = end date/time. If everything is formatted as date and/or time it is straightforward. If you have a date/time in excel and a cell with just 6 (to be the hours) you must convert the hours to days before adding (XL does dates and times in units of days) so:

Start + 6/24 = end

It does not matter if the work period is 24 hours. If you don't know the starttime, you will have to arbitrarily persume one, beginning of work period, middle, end, whatever makes the most sense. That will have to be up to your guidelines.

If the times are only in particular periods it gets more complex. You would have to provide a sample question to get some approaches.

Steve

2. ## task duration cacluation (all)

If I know the duration of a task... how can I use Excel to show the date/time it will be completed?

For example:

Work period is M-F 0800-1700, task duration is six hours, task begins on September 3 at 1400, task should end September 4 at 1100.

So I expect I will need the duration of the work period, and the start date/time.

What if the work period is 24 hours? - like a nonstop factory? what if I don't know the start time - should I presume it starts at the beginning of the work period?

Finally, some tasks are only done during the work period and others might be continuous. For example if an item is on ebay and there are 3 days left in the auction... that is continuous time.

3. ## Re: task duration cacluation (all)

Thanks Steve, I thought my example was clear (it is always clear when it is in your own head). Here is the example followed by the question that was implied but not actually asked.

Work period is 0800-1700, task duration is six hours, task begins on September 3 at 1400, task should end September 4 at 1100.

How can I use an Excel formula to show a completion date/time Sep 4, 1100 when I know the six task is only done during the work period (M-F 0800-1700) and it bagan on Sep 3 at 1400?

4. ## Re: task duration cacluation (all)

For working hours-only tasks, you can use a custom function:
<code>
Public Function EndTime(StartTime As Date, Duration As Double) As Date
Dim dtmStart As Date
Dim dtmEnd As Date
' Default return value
EndTime = StartTime
Duration = Duration / 24
Do While Duration > 0
dtmStart = Int(StartTime) + 8 / 24
If StartTime < dtmStart Then
StartTime = dtmStart
End If
dtmEnd = Int(StartTime) + 17 / 24
EndTime = StartTime + Duration
If EndTime > dtmEnd Then
Duration = Duration - (dtmEnd - StartTime)
StartTime = dtmStart + 1
If Weekday(StartTime, vbMonday) > 5 Then
StartTime = StartTime + 2
End If
Else
Duration = 0
End If
Loop
End Function
</code>
With the start time 09/03/2008 14:00 in cell A1 and duration 6 in cell B1, the formula =EndTime(A1,B1) will return 09/04/2008 11:00. Don't forget to format the cell with the formula as date+time.

5. ## Re: task duration cacluation (all)

Thank you Hans. If I wanted to account for a lunch hour during the workday, what would be the best way to do that? Would it be better to out in two columns with start lunch and stop lunch times? Even then, how would I reference them in the function?

6. ## Re: task duration cacluation (all)

Before I do anything, could you please provide a complete list of requirements? Do you need to take holidays such as Independence Day and New Year's Day into account? Will there be other requirements?

7. ## Re: task duration cacluation (all)

Sorry, Hans, i just thought about lunch hours and yes (thanks) I think it would be smart to include holidays - I can't think of anything else.

8. ## Re: task duration cacluation (all)

Will the four times involved (start of working day, start of lunch hour, end of lunch hour, end of working day) always be the same, or could they vary during the week or year?

9. ## Re: task duration cacluation (all)

Good point... I am inclined to say the times are static but, realistically, they could vary. I think I would go with static times, though, and live with any minor and occasional variations.

10. ## Re: task duration cacluation (all)

The attached workbook has a reference to atpvbaen.xls (the Analysis ToolPak - VBA add-in). If the reference shows as missing when you look at Tools | References in the Visual Basic Editor, you'll have to clear the reference and set it anew.

Code:

Public Function EndTime(StartTime As Date, Duration As Double, _
Optional Holidays, _
Optional Workstart = 8, Optional WorkEnd = 17, _
Optional LunchStart = 12, Optional LunchEnd = 13) As Date
Dim dtmWorkStart As Date
Dim dtmWorkEnd As Date
Dim dtmLunchStart As Date
Dim dtmLunchEnd As Date
Dim f As Boolean
' Default return value
EndTime = StartTime
Duration = Duration / 24
Do While Duration > 0
dtmWorkStart = Int(StartTime) + Workstart / 24
If StartTime < dtmWorkStart Then
StartTime = dtmWorkStart
End If
EndTime = StartTime + Duration
dtmLunchStart = Int(StartTime) + LunchStart / 24
dtmLunchEnd = Int(StartTime) + LunchEnd / 24
f = False
If StartTime <= dtmLunchStart And EndTime > dtmLunchStart Then
EndTime = EndTime + dtmLunchEnd - dtmLunchStart
f = True
End If
dtmWorkEnd = Int(StartTime) + WorkEnd / 24
If EndTime > dtmWorkEnd Then
Duration = Duration - (dtmWorkEnd - StartTime)
If f Then
Duration = Duration + dtmLunchEnd - dtmLunchStart
End If
StartTime = WorkDay(StartTime, 1, Holidays)
Else
Duration = 0
End If
Loop
End Function

The function now has 5 optional arguments:
Holidays is a range or array listing holidays; if omitted holidays are not taken into account.
WorkStart is the start of the working day, e.g. 8 or 8.5 (for 8:30); if omitted 8 is assumed.
WorkEnd is the end of the working day; if omitted 17 is assumed.
LunchStart is the start of the lunch period; if omitted 12 is assumed.
LunchEnd is the end of the lunch period; if omitted 13 is assumed.

It is up to the user to provide reasonable values, e.g. there is no check whether the lunch ends after it starts <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

There are some examples of use in the sample workbook; the list of holidays is fictitious.

11. ## Re: task duration cacluation (all)

This is fantastic Hans! Thank you so much. In looking at your code, I cannot tell how the holidays are excluded. How is that done?

12. ## Re: task duration cacluation (all)

The code uses the VBA version of the WORKDAY function from the Analysis ToolPak. This function has syntax

Workday(start_date,days,holidays)

where holidays is an array or a range listing holidays.

The function returns the date the specified number of days after the start_date, excluding weekend days and holidays from the supplied list. See WORKDAY for more info.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•