Results 1 to 12 of 12
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #2
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: task duration cacluation (all)

    For continuous tasks, simply add the start time and the duration/24.

    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. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #9
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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.
    Attached Files Attached Files

  11. #11
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •