Calculate number of Workdays in Excel using formula

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

NETWORKDAYS formula

NETWORKDAYS(start_date, end_date, [holidays])

NETWORKDAYS.INTL

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:

=NETWORKDAYS(TODAY(),TODAY()+30)

You get 21 days

Calculate number of working days between today and September 22, 2013 excluding holidays

=NETWORKDAYS(TODAY(),”09/22/2013″,B2:B19)

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.

Identifier WEEKEND DAYS
1 (or if left empty) 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

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:

String WEEKEND DAYS
1111111 Logically invalid
0000001 Sunday only
1100000 Monday and Tuesday
0110000 Tuesday and Wednesday
0011000 Wednesday and Thursday
0001100 Thursday and Friday
0000110 Friday qnd Saturday
0000001 Sunday only
1000000 Monday only
0100000 Tuesday only
0010000 Wednesday only
0001000 Thursday only
0000100 Friday only
0000010 Saturday only

=NETWORKDAYS.INTL(“5/18/2013″,”7/7/2013”,16)

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.

=NETWORKDAYS(“5/18/2013″,”7/7/2013”,B2:B19)

Returns 35 days

=NETWORKDAYS.INTL(“5/18/2013″,”7/7/1983”,1,B2:B19)

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

=NETWORKDAYS.INTL(“5/18/2013″,”8/06/2013″,”0000011”,B2:B19)

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

=NETWORKDAYS.INTL(“5/18/2013″,”8/06/2013″,”0000000”)

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 

4 COMMENTS

  1. Thanks for the knowledge, though I have not receive the core book.

  2. saidul islam saiyd saidul islam saiyd

    I subscribed but didn’t get the soft copy of this book.

  3. can i know which formula is for only less holiday not weekend .

    • Neha both of the formulas consider weekends. But to disregard weekends you can use NETWORKDAYS.INTL and take the string as “0000000” this way you will bypass weekends as days off.
      Hope this helps

Comments are closed.