You are here:

Excel/Schedule without weekends and holidays

Advertisement


Question
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 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 (day of the week, month, date, year) 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 each process needs to take place. I am thinking of jus 3 columns ... column A would have the department/function ... Column B would have the number of days that department can have to complete their function and Column C would be the actual date their function would need to be completed.  If we worked 7 days a week and holidays, the calculation would be simple. 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? I have Excel 2010.  If you would like the layout of the spreadsheet I started, please let me know.  Thank you.

Thanks, Joe
joebell30 at Comcast dot net

Answer
Joe,

the sister function to NETWORKDAYS is WORDAYS.  You can use the WORDAYS function to calculate what you want.  (the date after an elapsed number of workdays from a given date).  It also supports a list of Holidays like the NETWORKDAYS function.

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.