1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Calculate a Project End Date in Excel

By

1 of 2

Excel WORKDAY.INTL Function
Excel WORKDAY.INTL  Function

Excel WORKDAY.INTL Function

© Ted French

Calculate a Project End Date in Excel

Excel has several built in date functions that can be used to calculate the number of days between two dates.

Each date function does a different job so that the results differ from one function to the next. Which one you use, therefore, depends on the results you want.

Excel WORKDAY.INTL Function

Note: This function was first introduced in Excel 2010.

In the case of the WORKDAY.INTL function, it finds the start or end date of a project or assignment given a set number of work days.

Days specified as weekend days are automatically removed from the total. In addition, specific days, such as statutory holidays, can be omitted as well.

How the WORKDAY.INTL function differs from the WORKDAY function is that WORKDAY.INTL allows you to specify which days and how many are considered weekend days rather than automatically removing two days per week - Saturday and Sunday - from the total number of days.

Uses for this function include calculating:

  • the end date for a project with a set number of work days following a given start date
  • the start date for a project with a set number of work days before a given end date
  • the due date for an invoice
  • the expected delivery date for goods or materials

The WORKDAY.INTL Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the WORKDAY.INTL function is:

= WORKDAY.INTL ( Start_date , Days, Weekend, Holidays )

Start_date - (required) the start date of the chosen time period. The actual start date can be entered for this argument or the cell reference to the location of this data in the worksheet can be entered instead.

Days - (required) the length of the project. This is an integer showing the number of days of work that were performed on the project. For this argument, enter the number of days of work or the cell reference to the location of this data in the worksheet.

Note: To find a date that occurs after the Start_date argument use a positive integer for Days. To find a date that occurs before the Start_date argument use a negative integer for Days. In this second situation the Start_date argument would more properly be identified as the end date of a project.

Weekend - (optional) the number of days and which days of the week considered to be weekend days. These days are not included in the total number of working days. For this argument enter the weekend number code or the cell reference to the location of this data in the worksheet (see the table of number codes on page 2 of this tutorial).

Holidays - (optional) one or more additional dates that are excluded from the total number of working days. Use the cell references to the location of the data in the worksheet for this argument.

Example: Find the End Date of a Project

As seen in the image above, this example will use the WORKDAY.INTL function to find the end date for a project that begins August 5, 2012 and finishes 82 days later.

The weekend days in this example are considered to be Friday and Saturday so the number seven ( 7 ) will be entered for the Weekend argument

In addition, two holidays (August 19 and November 15) that occur during this period will be deducted from the total.

Note: To avoid calculation problems that can occur if dates are accidentally entered as text the DATE function will be used to enter the dates used in the function. See the Error Values section at the end of this tutorial for more information.

Entering the Data

  1. Enter the following data into the appropriate cell:
  2. D1:    Start Date:
    D2:    Number of Days: 
    D3:    Weekend:
    D4:    Holiday 1:
    D5:    Holiday 2:  
    D6:    End Date: 
    E1:    =DATE(2012,8,5)
    E2:    82
    E3:    7
    E4:    =DATE(2012,8,19)
    E5:    =DATE(2012,11,15)
    

Note: If the dates in cells E1, and E4 to E6 do not appear as shown in the image above, check to see that these cells are formatted to display data using the short date format.

Entering the WORKDAY.INTL Function

  1. Click on cell E6 to make it the active cell - this is where the results of the WORKDAY.INTL function will be displayed

  2. Click on the Formulas tab

  3. Choose Date and Time functions > WORKDAY.INTL from the ribbon to bring up the function's dialog box

  4. Click on the Start_date line in the dialog box

  5. Click on cell E1 in the worksheet to enter this cell reference into the dialog box

  6. Click on the Days line in the dialog box

  7. Click on cell E2 in the worksheet to enter this cell reference into the dialog box

  8. Click on the Weekend line in the dialog box

  9. Click on cell E3 in the worksheet to enter this cell reference into the dialog box

  10. Click on the Holidays line in the dialog box

  11. Drag select cells E4 and E5 in the worksheet to enter these cell references into the dialog box

  12. Click OK in the dialog box to complete the function

  13. The number of working days - 83 - should appear in cell E6 of the worksheet

  14. The date 11/29/2012 - the end date for the project - should appear in cell E6 of the worksheet

  15. How Excel calculates this date is:
    • The date that is 82 working days after August 5, 2012 is November 27 (the start date is not counted as one of the 82 days by the WORKDAY.INTL function)
    • Add to this date the two holiday dates specified (August 19 and November 15) that were not counted as part of the 82 Days argument
    • The end date of the project is, therefore, Thursday November 29, 2012
  16. When you click on cell E5 the complete function =WORKDAY.INTL ( E1, E2, E3, E4:E5 ) appears in the formula bar above the worksheet

WORKDAY.INTL Function Error Values

If the data for the various arguments of this function are not entered correctly the following error values appear in the cell where the WORKDAY function is located:

  • #VALUE! : appears in the answer cell If one of WORKDAY's arguments is not a valid date (if the date was entered as text for example)
  • #NUM!: appears in the answer cell if an invalid date results from adding the Start_date and Days arguments
  • If the Days argument is not entered as an integer - such as 83.25 days - the number will be truncated to the integer portion of the number: 83 days

©2014 About.com. All rights reserved.