Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Project End Time
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Project End Time
PD wrote:
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Project End Time
Excel 2007
Pretty much what smartin suggested: http://www.mediafire.com/file/eonktnyntnj/05_21_09.xlsx |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Project End Time
Thanks for the help - i'll have a go and see...
"Herbert Seidenberg" wrote: Excel 2007 Pretty much what smartin suggested: http://www.mediafire.com/file/eonktnyntnj/05_21_09.xlsx |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Project End Time
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" wrote: Excel 2007 Pretty much what smartin suggested: http://www.mediafire.com/file/eonktnyntnj/05_21_09.xlsx |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Project End Time
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Project Time | Excel Worksheet Functions | |||
Multiple user time collection by project and dates? | Excel Discussion (Misc queries) | |||
Running Total for Time Spent for a project in 1 month | Excel Worksheet Functions | |||
Project over time stacked bar chart | Charts and Charting in Excel | |||
Time calculation problem (URGENTProject due) | Excel Discussion (Misc queries) |