Thursday, November 18, 2010

Skip the Weekends Date Formula in Excel

I have a Date Started data in column A and Date Completed in column B. Column C I want to list how many days it took to complete the project so I subtract A from B (=B2-A2). However, I do not want to include weekends. So, if the start date is Friday and end date is Monday, currently my simple formula would display 4 days. I would like to create a formula that automatically skips over Saturday and Sundays, thus displaying the correct number of days as 2. How can this be accomplished?

Well, Excel actually has a built in function for this very reason. It is called NETWORKDAYS. It returns the number of whole workdays between two dates. To accomplish my task I simply use this formula, starting in cell C2: =NETWORKDAYS(A2,(B2-1)) Now you can calculate the number of work days there are between the start and end dates of a project by automatically excluding weekends. How cool is that?!