Pinterest Pixel

How to Calculate Workdays in Excel – 3 Useful Formulas

Microsoft Excel is a powerful data analysis and management tool, that offers a versatile range of applications.... read more

Download Excel Workbook
John Michaloudis
Posted on
Workdays in Excel

Steps To Follow

Overview

Workdays in Excel

Microsoft Excel is a powerful data analysis and management tool, that offers a versatile range of applications. It can be used to calculate workdays within a given month, making it an invaluable resource for keeping track of project deadlines, the duration of a task, calculating employee benefits, etc. This comprehensive analysis enables better decision-making and strategic planning.

In this article, we will explore the following functions to be able to calculate workdays in Excel –

Let us look at each of these methods in detail.

Download the Excel Workbook below to follow along and understand How to Calculate Workdays in Excel –
DOWNLOAD EXCEL WORKBOOK

 

NETWORKDAYS

Determining the count of working days manually can be quite challenging. It would be quite difficult and time-consuming to check your calendar and count weekdays week after week. Fortunately, Excel’s NETWORKDAYS Formula simplifies this task.

The NETWORKDAYS function returns the number of whole working days between start_date and end_date. It excludes the weekends in the count, and you can also provide a list of holidays for it to exclude as well in the count.

The syntax for this function is –

=NETWORKDAYS(start_date, end_date, [holidays])

where,

  • start_date – A date that represents the start date. Required.
  • ending date – A date that represents the end date. Required.
  • [holidays] – It contains a list of holidays that you need to exclude from your working calendar.
See also  How to Delete Every Other Row in Excel - Top 3 Methods

In this example, we have the start date and end date mentioned in cells A2 and B2, and the holiday list is mentioned in the cell range F2:F12.

Workdays in Excel

Follow the steps below to calculate the workdays in Excel –

STEP 1: Enter the NETWORKDAYS formula.

=NETWORKDAYS

Workdays in Excel

STEP 2: Enter the first argument i.e. start_date. It is mentioned in cell A2.

=NETWORKDAYS(A2

Workdays in Excel

STEP 3: Enter the second argument i.e. end_date. It is mentioned in cell B2.

=NETWORKDAYS(A2,B2)

Workdays in Excel

There are 66 working days between the dates of 1/1/2024 and 4/1/2024.

Workdays in Excel

Now, let us include the holiday list mentioned in cell range F2:F12 as the third argument.

STEP 4: Enter the third argument – holidays. It is mentioned in cell range F2:F12.

=NETWORKDAYS(A2,B2,F2:F12)

Workdays in Excel

Since 3 holidays come in between the start date and end date (1/1/2024, 1/15/2024, and 2/19/2024), there are 63 working days only.

 

NETWORKDAYS + EOMONTH

You have the start dates mentioned in column A and you want to know the workdays in a month. For this, you can use the EOMONTH function to get the last day of the month which is the indicated number of months before or after start_date.

This formula is useful when you want to know the number of working days in a month. Instead of typing the end date of each month individually, you can simply use the EOMONTH function to get the end date and then use NETWORKDAYS formula to get the number of working days in that month.

See also  How to Remove Leading and Trailing Spaces in Excel

The syntax of EOMONTH is –

=EOMONTH(start_date, months)

where,

  • start_date – A date that represents the start date. Required.
  • months – The number of months before or after start_date. A positive value for months returns a future date; a negative value returns a past date.

Follow the steps below to get the number of working days in a month –

STEP 1: Enter the NETWORKDAYS formula.

=NETWORKDAYS

Workdays in Excel

STEP 2: Enter the first argument i.e. start_date. It is mentioned in cell A2.

=NETWORKDAYS(A2

Workdays in Excel

STEP 3: Enter the EOMONTH formula to get the end date.

=NETWORKDAYS(A2,EOMONTH

Workdays in Excel

STEP 4: Enter the first argument i.e. start_date. It is mentioned in cell A2.

=NETWORKDAYS(A2,EOMONTH(A2

Workdays in Excel

STEP 4: Enter the second argument i.e. months. It should be 0 as you want to know the days in that month itself.

=NETWORKDAYS(A2,EOMONTH(A2,0))

Workdays in Excel

STEP 5: Press CTRL + D to copy the formula down.

This will give you the working days between the start date and the end of the month date.

Workdays in Excel

 

NETWORKDAYS.INTL

The NETWORKDAYS.INLT is a flexible version of the NETWORKDAYS formula. It is used to calculate the working days between the start and end date and additionally, it allows you to specify which days and how many of them should be considered non-working days. By default, it considers Saturday and Sunday as weekends.

See also  Excel Table Headers Show in Columns

This is particularly useful for organizations with non-standard weekends like Sunday-Monday, Sunday-only, etc.

The syntax of this formula is –

=NETWORKDAYS.INTL(start_date, end_date,[weekend], [holidays])

  • start_date – A date that represents the start date. Required.
  • ending date – A date that represents the end date. Required.
  • [weekend] – It indicates the days of the week that are weekend days.
  • [holidays] – It contains a list of holidays that you need to exclude from your working calendar.

The following are the weekend number values that indicate the following weekend days –

  • 1 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

Follow the steps below to get the number of working days in a month considering only Sunday as non-working day –

STEP 1: Enter the NETWORKDAYS.INTL formula.

=NETWORKDAYS.INTL

Workdays in Excel

STEP 2: Enter the first argument i.e. start_date. It is mentioned in cell A2.

=NETWORKDAYS.INTL(A2

Workdays in Excel

STEP 3: Enter the second argument i.e. end_date. It is mentioned in cell B2.

=NETWORKDAYS.INTL(A2,B2

Workdays in Excel

STEP 4: Enter the third argument i.e. weekend. It should be 11 as Sunday is considered a non-working day.

=NETWORKDAYS.INTL(A2,B2,11

Workdays in Excel

STEP 5: Enter the fourth argument i.e. holidays. It is mentioned in cell range F2:F12.

See also  How to Freeze Panes in Excel

=NETWORKDAYS.INTL(A2,B2,11,F2:F12)

Workdays in Excel

This will provide you with the number of working days between the start and end date mentioned excluding Sundays.

Workdays in Excel

 

Conclusion

You can harness the capabilities of Microsoft Excel for workday calculations and bring efficiency and accuracy to project management and scheduling. Whether utilizing the basic NETWORKDAYS function, incorporating EOMONTH for monthly analyses, or customizing workweek configurations with NETWORKDAYS.INTL, Excel has an in-built function for each purpose.

Download our 141 Free Excel Templates and Spreadsheets.

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!