Few days back I scribbled about calculating days between two dates and there I discussed DAYS360 formula method and one other common sense method to find out the number of days between two dates. But what if we want to find out working days between two dates and not just any days i.e. excluding weekends and/or holidays?
Our requirements concerning computation might vary but generally we can have following situations:
- Compute number of working days between two dates or within specific days from specific date. This is often important for project management decisions as you have to know how many effective days you have to finish the task. – For this we use NETWORKDAYS or NETWORKDAYS.INTL formula
- Finding what will be the day or date after specific number of workdays (not just any day but working days) from particular date. We often need to find this out where agreements determine due dates considering workdays only – For this we use WORKDAY or WORKDAY.INTL formula. I will explain these formulae in another article.
- WORKDAYS Formulas in Excel
- Calculate number of workdays 30 days from today
- Calculate number of working days between today and September 22, 2013 excluding holidays
- Calculate number of workdays between two dates
- Calculate number of workdays from specified date excluding different weekends
- Calculate number of workdays between two dates excluding holidays with Saturday and Sunday as weekend days
- Calculate number of workdays between two dates excluding holidays and different weekends
- Calculate then number of workdays with no weekends and no holidays
WORKDAYS Formulas in Excel
NETWORKDAYS(start_date, end_date, [holidays])
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
Starting_date: the date from which you want start your calculation
End_date: the date up to which you want calculation to be done
Weekend (optional) (only in .INTL formula): An identifier number or a boolean-like string of 0s and 1s. It is an optional formula field and nothing is mentioned by the user then default value of 1 or 000011 will be considered i.e. Saturday and Sunday will be considered as Weekend days. This option is available only in .INTL version of formula which is an extension of the simple formula. I will explain the use of string in an example below.
Holidays (optional): Represents holidays and needs to be excluded from calculation. It can be a date, or a reference to cell that contains date or a range of cells containing multiple dates. If this formula field is left empty then excel considers there are no holidays in between two dates.
Caution: If you are hard coding dates in right in the formula then enclose them in quotes or use DATE function so that excel correctly renders the result.
Calculate number of workdays 30 days from today
To calculate working days 30 days from today, if today is May 18, 2013 then use the following formula:
You get 21 days
Calculate number of working days between today and September 22, 2013 excluding holidays
Resulted 87 days
Calculate number of workdays between two dates
=NETWORKDAYS(“5/18/2013″,”5/18/2014”) you can also specify the cell references that contain dates. Or if you are feeling more geeky you can play with DATE formula within this formula.
Calculate number of workdays from specified date excluding different weekends
In many countries weekend days are Saturday and Sunday and by default excel considers Saturday and Sunday as week end days. But in some countries week end days are Thursday and Friday and yet other possibility is just one week end day e.g. Friday or Sunday. To cater this we use NETWORKDAYS.INTL formula and in formula we specify the identifier that tells excel which day or days to consider as week end days.
|1 (or if left empty)||Saturday, Sunday|
If remembering numbers are too difficult for you than you can let excel know using string i.e. 1 as day off and 0 as day on. So 0010000 means that Wednesday is off and the rest of the days are on. Simple ain! Remember, string always starts from Monday and has seven digits in it for each day.
Following table gives you examples of strings and what they mean:
|1100000||Monday and Tuesday|
|0110000||Tuesday and Wednesday|
|0011000||Wednesday and Thursday|
|0001100||Thursday and Friday|
|0000110||Friday qnd Saturday|
Results in 44 days where 16 is an identifier number and represents only Friday as week end day.
Calculate number of workdays between two dates excluding holidays with Saturday and Sunday as weekend days
Talking about holidays if there is one holiday than you can specify the date if there are multiple holidays then you can have the dates in range and specify that range in formula to determine working days.
Returns 35 days
Returns 35 days. Because 1 is an identifier number that means Saturday and Sunday as weekend days. That is why it returned the same result. Also NETWORKDAYS formula that is without .INTL excludes Saturday and Sunday as weekend days by default.
Calculate number of workdays between two dates excluding holidays and different weekends
Returns 24 days. 0000011 means that Monday through Friday days are on where as Saturday and Sunday are off. Note that you have to put the string in quotes. String will always be 7 digits long representing 7 days of the week.
Calculate then number of workdays with no weekends and no holidays
Returns 81 days. We have bypassed weekends as they are also working days. In some industries employees work whole week with no weekends as work is managed in shifts. In that case weekends are not applicable. So we can use this formula to calculate number of working days.
There you have it. Use of often overlooked formula or may be a bit new for many as it was introduced in Excel 2010. How are you using this formula in your work or if you have any interesting use of this formula I will be glad to read that in the comments below. I should also upload on example workbook containing above examples but lacking motivation 😉 Will do it soon