Project End Time

[PD]

Hi,

I've looked everywhere and can't find an answer for this one..
I am trying to calculate what time a task will finish, however it needs to
be quite exact with differing operating hours mon-fri than sat-sun. Is there
any way to do this?

Info Example:

Start - 09:00 mon 18/5/09
Duration - 60 hrs
Mon-fri hrs work - 10hrs (each day)
Sat-sun - 3hrs (each day)

= projected finish time 13:00 25/5/09

Also, this is a repeatable task, so if possible would like to drag this
formula down and calculate when, for example, the tenth would finish

Many thanks in advance for any help you can give

 

[smartin]

Hi,

I've looked everywhere and can't find an answer for this one..
I am trying to calculate what time a task will finish, however it needs to
be quite exact with differing operating hours mon-fri than sat-sun. Is there
any way to do this?

Info Example:

Start - 09:00 mon 18/5/09
Duration - 60 hrs
Mon-fri hrs work - 10hrs (each day)
Sat-sun - 3hrs (each day)

= projected finish time 13:00 25/5/09

Also, this is a repeatable task, so if possible would like to drag this
formula down and calculate when, for example, the tenth would finish

Many thanks in advance for any help you can give

Never tried it, but FWIW here's how I would approach it. A
Gedankenexperiment, if you will.

First create a table of dates, every day from whenever a project could
start until sufficiently far into the future. In the table, add columns
for earliest start work time, latest end work time, a calculation of the
difference of those times (expressed in hours) and a running total of
the hours from /previous/ days. Why previous days? It will be easier to
show, on each day, how many hours were burned from /previous/ days'
schedules. Since your cycle cycle repeats weekly this should not be
difficult to set up.

Next order of business is to locate the project's start date in the date
table, and how many hours were burned before the project starts. VLOOKUP
should do it here. Subtract the start work time from the schedule from
the project's start time component (in hours) and add the result to the
running total to get a baseline start point for the project.

The project will take 60 hours. So add 60 to the baseline value. We need
to find the greatest value in the running totals that does not exceed
this. MATCH can do this, using the -1 option (I think). Now you know
which day the project will complete. With a little addition and
subtraction you will know the exact day and time the project will complete.

To repeat the task, just set the start time of the second instance equal
to the finish time of the previous instance and apply the same formulas
as above.

 

[Herbert Seidenberg]

Excel 2007
Pretty much what smartin suggested:
http://www.mediafire.com/file/eonktnyntnj/05_21_09.xlsx

 

[PD]

Thanks for the help - i'll have a go and see...

 

[PD]

Hi Herbert,

Thanks for the response, however my Excel is version 2003 and while your
link does open and seem to work (i.e. defined names are all there) the
"Accum" list and Finish box all have #VALUE error. When I change the Accum
list to a {} Ctrl+Shift+Ent formula, it warns of a circular reference. This
looks exactly like what I need though, do you have any suggestions ?

 

[Herbert Seidenberg]

In Excel 2003
MRound() does not work with arrays.
Take it out of the "Accum" formula
and it will work most of the time.
"Accum" is not entered as an array formula.
Apply the defined name "Accum" manually.