In this Article
This tutorial demonstrates how to use the Excel WORKDAY Function in Excel to add business days.
WORKDAY Function Overview
The WORKDAY Function Returns the work day n number of working days (working days excludes weekends: Sat & Sun) from a start date. Optionally, you can exclude holidays. For custom weekends, use WORKDAY.INTL instead.
To use the WORKDAY Excel Worksheet Function, Select cell and Type:
(Notice how the formula input appear)
WORKDAY Function syntax and Inputs:
|
1 |
=WORKDAY(start_date,days,holidays) |
start_date – The start date in Excel serial number format or entered as a date with quotations (“s) surround the date. Example: You can not enter 11/12/2015 directly into the cell. Instead you need to enter “11/12/2015” or you would need to use the corresponding serial number: 42320. Alternatively, you can reference a cell with the date 11/12/2015 entered. Excel automatically converts dates stored in cells into serial format (unless the date is entered as text).
days – The number of non-weekend and non-holiday days from the start date.
holidays – OPTIONAL. An array with a list of dates representing holidays that should be excluded from workdays.
Find Nearest Workday
To find the nearest workday use the WORKDAY Function:
|
1 |
=WORKDAY(B3,C3) |
Find Nearest Workday to Today
To find the nearest workday to today use the TODAY Function with the WORKDAY Function:
|
1 |
=WORKDAY(TODAY(),B3) |
Find Nearest WORKDAY in 10 Days
This example will find the nearest workday 10 days from the start date:
|
1 |
=WORKDAY(B3,C3) |
You can also use negative days to look for workdays in the past:
Workday with Holidays
By default, WORKDAYS will ignore all holidays. However you can use a 3rd optional argument to define a range of holidays:
|
1 |
=WORKDAY(B3,C3,F3:F4) |
WORKDAY in Google Sheets
The WORKDAY Function works exactly the same in Google Sheets as in Excel:
WORKDAY Examples in VBA
You can also use the WORKDAY function in VBA. Type:
Application.Worksheetfunction.Workday(start_date,days,holidays)
Executing the following VBA statements
|
1 2 3 |
Range("E2") = Application.WorksheetFunction.WorkDay(Range("B2"), Range("C2")) Range("E3") = Application.WorksheetFunction.WorkDay(Range("B3"), Range("C3")) Range("E4") = Application.WorksheetFunction.WorkDay(Range("B4"), Range("C4"), Range("D4")) |
will produce the following results
For the function arguments (start_date, etc.), you can either enter them directly into the function, or define variables to use instead.
Return to the List of all Functions in Excel
