Find a Project's Start or End Date in Google Spreadsheets

01
of 03

The WORKDAY.INTL Function

Google Spreadsheets WORKDAY.INTL Function
Google Spreadsheets WORKDAY.INTL Function. © Ted French

Find a Project Start or End Date in Google Spreadsheets

Google Spreadsheets has several built in date functions that can be used for work day calculations.

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.

Google Spreadsheets WORKDAY.INTL Function

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 the WORKDAY.INTL 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 function is:

= WORKDAY.INTL ( start_date, num_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

num_days - (required) the length of the project
- for this argument, enter an integer showing the number of days of work that were performed on the project
- enter the actual number of days of work - such as 82 - or the cell reference to the location of this data in the worksheet
- to find a date that occurs after the start_date argument, use a positive integer for num_days
- to find a date that occurs before the start_date argument, use a negative integer for num_days

weekend - (optional) indicates which days of the week are considered to be weekend days and excludes these days from 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
- if this argument is omitted, the default 1 (Saturday and Sunday) is used for the weekend code
- see the complete list of number codes on page 3 of this tutorial

holidays - (optional) one or more additional dates that are excluded from the total number of working days
- holiday dates can be entered as serial date numbers or the cell references to the location of the date values in the worksheet
- if cell references are being used, date values should be entered into the cells using the DATE, DATEVALUE or TO_DATE functions to avoid possible errors

Example: Find the End Date of a Project with the WORKDAY.INTL Function

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

Two holidays (September 3 and October 8) that occur during this period will not be counted as part of the 82 days.

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 as arguments. See the Error Values section at the end of this tutorial for more information.

Entering the Data

A1:    Start Date:
A2:    Number of Days: 
A3:    Holiday 1:
A4:    Holiday 2:  
A5:    End Date: 
B1:    =DATE(2012,7,9)
B2:    82
B3:    =DATE(2012,9,3)
B4:    =DATE(2012,10,8)
  1. Enter the following data into the appropriate cell:

If the dates in cells b1, B3, and B4 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.

02
of 03

Entering the WORKDAY.INTL Function

Google Spreadsheets WORKDAY.INTL Function
Google Spreadsheets WORKDAY.INTL Function. © Ted French

Entering the WORKDAY.INTL Function

Google spreadsheets do not use dialog boxes to enter a function's arguments as can be found in Excel. Instead, it has an auto-suggest box that pops up as the name of the function is typed into a cell.

  1. Click on cell B6 to make it the active cell - this is where the results of the WORKDAY.INTL function will be displayed
  2. Type the equal sign ( = ) followed by the name of the function workday,intl
  3. As you type, the auto-suggest box appears with the names and syntax of functions that begin with the letter W
  4. When the name WORKDAY.INTL appears in the box, click on the name with the mouse pointer to enter the function name and open round bracket into cell B6

Entering the Function Arguments

As seen in the image above, the arguments for the WORKDAY.INTL function are entered after the open round bracket in cell B6.

  1. Click on cell B1 in the worksheet to enter this cell reference as the start_date argument
  2. After the cell reference, type a comma ( , ) to act as a separator between the arguments
  3. Click on cell B2 to enter this cell reference as the num_days argument
  4. After the cell reference, type another comma
  5. Click on cell B3 to enter this cell reference as the weekend argument
  6. Highlight cells B4 and B5 in the worksheet to enter these cell references as the holiday argument
  7. Press the Enter key on the keyboard to enter a closing round bracket " ) " after the last argument and to complete the function
  8. The date 11/29/2012 - the end date for the project - should appear in cell B6 of the worksheet
  9. When you click on cell b5 the complete function
    = WORKDAY.INTL ( B1, B2, B3, B4 : B5 ) appears in the formula bar above the worksheet

The Math behind the Function

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

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
03
of 03

Table of Weekend Number Codes and Corresponding Weekend Days

Google Spreadsheets WORKDAY.INTL Function
Google Spreadsheets WORKDAY.INTL Function. © Ted French

Table of Weekend Number Codes and Corresponding Weekend Days

For Locations with a Two Day Weekend

Number        Weekend days 
1 or omitted    Saturday, Sunday 
      2              Sunday, Monday
      3              Monday, Tuesday
      4              Tuesday, Wednesday
      5              Wednesday, Thursday
      6              Thursday, Friday
      7              Friday, Saturday

For Locations with a One Day Weekend

Number      Weekend day 
     11              Sunday
     12              Monday 
     13              Tuesday
     14              Wednesday
     15              Thursday
     16              Friday
     17              Saturday 
Format
mla apa chicago
Your Citation
French, Ted. "Find a Project's Start or End Date in Google Spreadsheets." ThoughtCo, Aug. 23, 2016, thoughtco.com/find-a-projects-start-or-end-date-3123454. French, Ted. (2016, August 23). Find a Project's Start or End Date in Google Spreadsheets. Retrieved from https://www.thoughtco.com/find-a-projects-start-or-end-date-3123454 French, Ted. "Find a Project's Start or End Date in Google Spreadsheets." ThoughtCo. https://www.thoughtco.com/find-a-projects-start-or-end-date-3123454 (accessed January 22, 2018).