Eliminate weekends and holidays from schedule

Hi All,

I attached a sample spreadsheet to help explain what I am trying to do. I work in a printing production facility and the customer usually indicates the date the project needs to be completed and when it will ship. We have various departments that perform various functions and each department requires a specific number of days to complete their portion of the project. What we would like to do before the job enters the facility is to ask the customer what date the project needs to ship. We would then enter that date along with the number of days required to complete each function. We would like the spreadsheet to calculate which date each department needs to complete their part of the project based on the number of days each department needs to perform their function. Then, we would be able to know what date the process of receiving the order needs to take place. If we worked 7 days a week and holidays, the calculation would be simple (as in my example). However, we only schedule work 5 days a week and do not work on holidays. Is there a way to run these calculations and omit Saturdays, Sundays and holidays from the calculation? I know the NETWORKDAYS function, but that only tells me how many work days between 2 dates. I need to work backwards from the due date. Any way to do this?

Thanks, Joe

Thanks newdoverman, but I am confused because it doesn't seem to be working for me. I am not sure what you mean should be entered in C2. Is there any way to enter the final date in C17 and have the spreadsheet count backwards to C2 while subtracting the number of days in Column B for each entry? Also, are both columns C and D necessary? Thanks!

Perfect ... that is exactly what I needed. Thanks!

Newdoverman,

I need to ask you another question regarding the spreadsheet you assisted me with. I just found out that the version that is going to be available to me is not 2010, but 2007. So, I did some research and modified your formula slightly to WORKDATE STARTDATE and it works with 2007. Now, there is another little issue I need to work on. It seems that there are going to be some constant dates in this spreadsheet ... for example, there will always be 7 days from Activity 1 to Activity 13 and there will always be 6 days from Activity 2 to Activity 13 and there will always be 4 days from Activity 4 to Activity 13. There can be others with the same structure and some in which we can just add the number of days in column B.

I need to know if there is a formula I can add between any 2 specific Activities that will give me a specified number of days while still observing holidays, Saturdays and Sundays. I hope I explained that correctly and that it makes sense. Thanks!