Calculate Work Days in Excel Using the following Functions
Learn how to calculate work days for a given year, or multiple years, in Excel. There are multiple functions that you can use to determine work days. Let’s take a look at three of them now. Depending out your needs, these can be used to fit your application.
NETWORKDAYS Syntax:
The NETWORKDAYS function in Excel allows you to return the number of workdays between two specified dates. For a detailed explanation with a downloadable demonstration, check out the complete example.
=NETWORKDAYS(start_date, end_date, [holidays])
- Start_date – Initial Date
- End_date – The end of the period to stop counting workdays.
- Holidays – (Optional) Omitted from being counted. Need to specify in separate list.
NETWORKDAYS.INTL Syntax
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
Arguments:
- start_date – (required) Can be earlier than the end_date, later than end_date, or same as end_date.
- end_date – (required) The end date
- [weekend] – (optional) If omitted, the default weekend will be Saturday & Sunday. See the table below Fig 1.1
- [holidays] – (optional) Holiday’s are specified. They can be a single cell or an array of cells. There are no default holiday’s in Excel
Fig. 1.1
Weekend Number | Weekend Days |
0 or omitted | Saturday, Sunday |
2 | Sunday, Monday |
3 | Monday, Tuesday |
4 | Tuesday, Wednesday |
5 | Wednesday, Thursday |
6 | Thursday, Friday |
7 | Friday, Saturday |
11 | Sunday only |
12 | Monday only |
13 | Tuesday only |
14 | Wednesday only |
15 | Thursday only |
16 | Friday only |
17 | Saturday only |
Click here for a detailed overview of the NETWORKS.INTL Function
DAYS360 Syntax:
The DAYS360 function in Excel calculates the number of days between two dates using a 360-day year.
=DAYS360 (start_date,end_date,[method])
- start_date – (required) Must be valid starting date
- end_date – (required) – Must be valid ending date
- method – (optional) – Type of day count
- False (omitted) – US (NASD) method used.
- If the starting date is the last day of the month, it is set as the 30th day of the same month.
- If the start date is the last day of the month, the end date is set to the 1st of the next month. The end date is the set to the 30th of that month.
- True – European method used. The starting dates and ending dates that occur on the 31st day of a month become equal to the 30th day of the same month.
- False (omitted) – US (NASD) method used.