Thread: 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