How to Get the Number of Weekdays or Workdays Between Two Dates

by

In this tutorial, we will explore the WEEKDAYS and NETWORKDAYS functions, which are specifically designed to help us get the number of workdays or weekdays between two dates.

Using the WEEKDAYS Function

The WEEKDAY function in Excel is used to return the day of the week for a given date. The function returns a number between 1 and 7, with 1 representing Sunday and 7 representing Saturday.

The syntax for the WEEKDAY function is as follows:

WEEKDAY(serial_number, [return_type])

Where:

  • serial_number is the date that you want to return the day of the week for. This can be entered as a date, a cell reference, or as the result of another function.
  • [return_type] is an optional argument that determines the numbering system used to return the day of the week.
    • 1 returns the day of the week as a number between 1 (Sunday) and 7 (Saturday).
    • 2 returns the day of the week as a number between 1 (Monday) and 7 (Sunday).
    • 3 returns the day of the week as a number between 0 (Monday) and 6 (Sunday).
    • 11 returns the day of the week as a number between 1 (Monday) and 7 (Sunday) and is used in international systems.
    • 12 returns the day of the week as a number between 0 (Sunday) and 6 (Saturday) and is used in international systems.

If the return_type argument is omitted, the function defaults to 1.

In the example above, the formula returns the value “5”, which is the 5th day of the week (Thursday).

Using the NETWORKDAYS Function

The NETWORKDAYS function in Excel is used to calculate the number of workdays (i.e., weekdays excluding weekends) between two dates. This function is particularly useful for calculating the number of workdays between two dates, such as the number of working days between a start date and an end date or the number of workdays to complete a project.

The syntax for the NETWORKDAYS function is as follows:

NETWORKDAYS(start_date, end_date, [holidays])

Where:

  • start_date is the start date of the range you want to calculate workdays for.
  • end_date is the end date of the range you want to calculate workdays for.
  • [holidays] is an optional argument that lists the dates that you want to exclude from the calculation. This can be a range of cells, an array, or a list of dates.

This function tells Excel to count all the dates between July 1, 2021, and August 1, 2021 that are not weekends or holidays.

Related Posts: