Software

Use Excel to calculate the number of working days you have to complete your projects

When you're trying to determine how many days you have left before reaching a project deadline, try this handy Excel technique.

You don't need to count out days on a calendar to find out how many days you have to complete a project. Excel's NETWORKDAYS function can calculate it for you -- without requiring you to work weekends or holidays.

To set up a worksheet that will calculate the number of working days excluding weekends and holidays from a project's start date to its due date, follow these steps:

  1. Enter the headings for Project, Start Date, End Date, and Work Days as shown.
  2. Select B2:C2.
  3. Press Shift+ Ctrl + Down Arrow.
  4. Right-click the selection and choose Format Cells.
  5. Select Date under Category and 3/14/01 under Type.
  6. Click OK.

  1. Select J2.
  2. Press Shift+ Ctrl + Down Arrow.
  3. Right-click the selection and choose Format cells.
  4. Select Date under Category and 14-Mar-01 under Type.
  5. Click OK.
  6. Complete the Holiday Table as shown.
  7. Select J2:J9.
  8. Click in the Name box and enter Holidays_2008.

  1. Click D2 and enter the following formula:

=NETWORKDAYS(B2,C2,Holidays_2008)

  1. Copy the formula in D2 to the D3:D4 to obtain the number of Workdays for Projects B and C.


Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

15 comments
ken
ken

The functions here will work up to 9999. Any extra bank holidays can be added in a similar manner to the 5th June Diamond jubilee one. The EasterSunday function was not written by me but comes from the ERLANDSEN DATA CONSULTING site and is, I think, the sweetest bit of code I've seen. Function EasterSunday(InputYear As Integer) As Long ' Returns the date for Easter Sunday, does not depend on Excel Dim d As Integer d = (((255 - 11 * (InputYear Mod 19)) - 21) Mod 30) + 21 EasterSunday = DateSerial(InputYear, 3, 1) + d + (d > 48) + 6 - _ ((InputYear + InputYear \ 4 + d + (d > 48) + 1) Mod 7) End Function Function WrkDays(StartDate As Date, EndDate As Date) As Integer Dim d& Dim Cnt& Dim DC As Date Dim WD& Dim A& Dim M& Dim NY& Dim ES As Date Dim EM& Dim Yno Dim DN& Dim CD As Date Dim BD As Date 'Dim ES As Date 'It is assumed that there will only be 1 easter during the spell 'Check for a weekend d = EndDate - StartDate DC = StartDate A = 0 NY = 0 'Stop For Cnt = 1 To d Step 1 WD = Weekday(DC, vbSunday) DN = DatePart("d", DC) Select Case WD Case 1, 7 'This is a weekend A = A Case Else 'This is a weekday 'Check for a bank holiday 'Get the month from the date DC M = Month(DC) Select Case M Case 1 'January 'NYD is first weekday 'Check the day number 'is the start date in the first week 'Find the day of January 1 CD = DateValue("1/1/" & Year(DC)) 'What day of the week is it? WD = Weekday(CD) Select Case WD Case 1 'Sunday 'nyd Tomorrow If DC = CD + 1 Then A = A Else A = A + 1 End If Case 2, 3, 4, 5, 6 'Monday, Tuesday, Wednesday, Thursday or Friday 'NYD today A = A Case 7 'Saturday 'NYD Monday If DC = CD + 2 Then A = A Else A = A + 1 End If End Select Case 2 'February 'No current holidays A = A + 1 Case 3 'March 'Earliest dates for Easter 'Get the date of easter ES = EasterSunday(Year(DC)) EM = Month(ES) If EM = M Then 'Easter is this month If DC ES - 2 Then A = A + 1 ElseIf DC ES + 1 Then A = A + 1 End If Else A = A + 1 End If Case 4 'April 'Late dates for Easter 'Get the date of easter 'Stop ES = EasterSunday(Year(DC)) EM = Month(ES) If EM = M Then 'Easter is this month If DC = ES - 2 Then A = A GoTo ED ElseIf DC = ES + 1 Then A = A GoTo ED Else A = A + 1 End If Else A = A + 1 End If ED: Case 5 'May DN = DatePart("d", DC) Select Case DN Case 1 To 7 'First monday in May Select Case WD Case 2 'This is a Monday Case Else 'The other days are working days A = A + 1 End Select Case 25 To 31 'Last Monday in May Select Case WD Case 2 'This is a Monday Case Else 'The other days are working days A = A + 1 End Select Case Else A = A + 1 End Select Case 6 'June 'No Current Holidays 'Diamond Jubilee 2012 only 5th June 'is the year 2012? 'Specific code for extra bank holidays 'Select the year Yno = Year(DC) 'Check if this year applies to the bank holiday If Yno = 2012 Then 'If the year is relevant then do the following DN = DatePart("d", DC) 'Get the day number If DN 5 Then 'Check if the day number matches the day of the bank holiday A = A + 1 'If it does not match add 1 to the working days total End If Else A = A + 1 End If Case 7 'July 'No Current holidays A = A + 1 Case 8 'August 'Last Monday in August DN = DatePart("d", DC) Select Case DN Case 25 To 31 'Last Monday in May Select Case WD Case 2 'This is a Monday Case Else 'The other days are working days A = A + 1 End Select Case Else A = A + 1 End Select Case 9 'September 'No Current Holidays A = A + 1 Case 10 'October 'No current Holidays A = A + 1 Case 11 'November 'No Current Holidays A = A + 1 Case 12 'December '25th Christmas '26th Boxing day 'If Christmas or Boxing day is a weekend then the next weekday is used. Yno = Year(DC) CD = DateValue("25/12/" & Year(DC)) BD = DateValue("26/12/" & Year(DC)) WD = Weekday(CD, vbSunday) Select Case WD Case 1, 7 'Christmas day is a Saturday or Sunday 'The following Monday and Tuesday are bank holidays If WD = 1 Then If DC = CD + 1 Then A = A ElseIf DC = CD + 2 Then A = A Else A = A + 1 End If ElseIf WD = 7 Then If DC = CD + 2 Then A = A ElseIf DC = CD + 2 Then A = A Else A = A + 1 End If End If Case 2, 3, 4 'Christmas day is a Monday, Tuesday, Wednesday or Thursday 'Today and Tomorrow are bank holidays If DC = CD Then A = A ElseIf DC = CD + 1 Then A = A Else A = A + 1 End If Case 6 'Christmas day is a Friday 'Friday and the following Monday are bank holidays If DC = CD Then A = A ElseIf DC = CD + 3 Then A = A Else A = A + 1 End If Case Else A = A + 1 End Select End Select 'A = A + 1 End Select DC = DC + 1 Next Cnt WrkDays = A End Function

sciencegal
sciencegal

When making your list for Holidays simply follow that with all the weekend dates for the year. Then make sure the holiday dates and weekend dates are selected together. Use the formula provided in this example and it works! Please remember you might have to add the add in. Go to the help search and type in NETWORKDAYS where it will provide direction as to how to get the add in and other helpful hints. Really glad I found this on the web, saved me a ton of time!!!!!! THANK YOU!

MSH1971
MSH1971

This techneque is to count the number of working days., Pl some one help to find a formula to skip the saturday, sunday and holidays between two task dates... eg start 1/9 end 1/31.. total 31days. need a formula that excel skip automaticall these days when calculating the days.

AbaidM
AbaidM

This was excellent tip. Saved me hours of work.

Jurgen_Lobert
Jurgen_Lobert

Thanks for this function. I seem to be brain-dead on ideas: can you use this to calculate the DATE after a certain number of working days? I have a start date and need to calculate the due (end) date for the project report based on 10 working days.

Jurgen_Lobert
Jurgen_Lobert

Thanks for this function. I seem to be brain-dead on ideas: can you use this to calculate the DATE after a certain number of working days? I have a start date and need to calculate the due (end) date for the project report based on 10 working days.

kloefgren
kloefgren

If you replace the date in the "start date" cell with the TODAY( )function, the spreadsheet will calculate the current number of work days remaining to the "end date".

ksawaneh
ksawaneh

Hai, can some one help me, a guideline to write VBA. Introduction

DaleSprague
DaleSprague

Can you use or know how to do this same function with a Sharepoint list?

blessing.francis
blessing.francis

This is really cool I gave it a try and it worked out for me.Thanks for the article its a wonderful way to track the progress of my project. Thank you Blessing www.xpertva.com

tolmr
tolmr

I think you meant shift+down arrow. ctrl+shift+down arrow does weird things. thanks for the tip.

gururao.1976
gururao.1976

What is the Version of MS Excel should be installed in the System.

Jurgen_Lobert
Jurgen_Lobert

=WORKDAY(StartDate,NumberOfWorkDays,ListOfHolidays)

kbashir
kbashir

I don't think in this case Excel version have any issue. As long the version you have supports Network days the formula will work.

bobwright
bobwright

If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. How? On the Tools menu, click Add-Ins. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK. If necessary, follow the instructions in the setup program.

Editor's Picks