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.

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?

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.

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.