Related Tutorial: If you are unfamiliar with Excel's date functions you may want to try the Excel WORKDAY Function Step by Step Tutorial which include more detailed information about this function.
The WORKDAY function can be used to calculate the end date or start date of a project for a given number of business days. The number of business days cannot include weekend days. Specific holidays can also be omitted.
The syntax for the WORKDAY function is:
= WORKDAY ( Start_date , Days , Holidays )
Start_date - the start date of the project or time period in question.
Days - the number of days work on the project occurred after the Start_date.
Holidays - can be used to exclude one or more dates from the total number of working days (optional).
Example: Calculating a Project End Date
For help with this example, see the image above.
- Enter the following data into the appropriate cell:
D1 - Start Date:
D2 - Days:
D3 - Holiday 1:
D4 - Holiday 2:
D5 - End Date:
E1 - 7/6/2009
E2 - 87
E3 - 9/7/2009
E4 - 10/12/2009
Note: If the dates in cells E1, E3, and E4 do not appear correct, check to see that these cells are formatted for dates.
- Click on cell E5 - the location where the results will be displayed.
- Click on the Formulas tab.
- Choose Date and Time functions > WORKDAY from the ribbon to bring up the
function's dialog box.
- Click on the Start_date line in the dialog box.
- Click on cell E1 in the spreadsheet to enter that cell reference into the dialog box as the Start_date argument.
- Click on the Days line in the dialog box.
- Click on cell E2 in the spreadsheet to enter that cell reference into the dialog box as the Days argument.
- Click on the Holidays line in the dialog box.
- Drag select cells E3 and E4 in the spreadsheet to enter these cell references into the dialog box as the Holidays argument.
- Click OK in the dialog box.
- The end date for the project - 11/6/2009 (November 6, 2009) appears in cell E5.
- If a number, such as 40123, appears in cell E5 its likely that the cell has General formatting applied to it. Change the cell formatting to Short Date to get the results to appear correctly.
- When you click on cell E5 the complete function
= WORKDAY ( E1 , E2 , E3 : E4 ) appears in the formula bar above the worksheet.
Note: The WORKDAY function can also be used to find a date in the past - such as the day work began on a project. To do so enter a negative number for the Days argument and use the project's end date for the start_date argument.Related Tutorials