How to Calulate End Time for Working Hours?

I have a gantt chart which is to show the projected completion times (date & time) for a given group of tasks with fixed durations (hours). The gantt chart is listed on one worksheet, and the working hours are listed on another worksheet.

I need to calculate the end time (Date & Time) for each task, but the calculation must only count working hours. No time outside of the working hours, and no weekends should be used when calculating these figures.

Can someone suggest a function which can handle this task? So far I have only found tips regarding hour to calculate the duration from a given start and end time, but that is kind of the opposite of what I’m going for.

Thank you for all the help!

This type of calculation is trickier than it might appear......

using WORKDAY function from Analysis ToolPak add-in you could use this formula in F2 copied down

=WORKDAY(E2,(B2+MOD(E2,1)-Workdays!$B$2)/(Workdays!$B$3-Workdays!$B$2))+MOD(B2+MOD(E2,1)-Workdays!$B$2,Workdays!$B$3-Workdays!$B$2)+Workdays!$B$2

DaddyLongLegs,

That worked perfectly for what I needed. Thank you!

Incase anyone anyone else can make use of this function, here is an explanation of same.

=WORKDAY(StartTime,(Duration+MOD(StartTime,1)-$StartWorkday)/($EndWorkday-$StartWorkday))+MOD(Duration+MOD(StartTime,1)-$StartWorkday,$EndWorkday-$StartWorkday)+$StartWorkday

This worked great and i have looked at the sheet that you are using. It is a great template and something i have been trying to do for a while. I have used these ideas and altered my sheets to accomodate for my info rather than yours.

I am having a problem with the gannt chart info though.

I have used your sheet as a template for me and when i have entered my information into it the chart is not picking the correct information up. It is still picking up the start date as 21/07/2008, instead of the start date in the data field.

Please can you help as i have tried a few things and cannot get it to work.

Thank you in advance

Matt