WORKDAY function to calculate number of working days in Excel

WORKDAY function is designed to determine the start or end date of an event relative to a given starting or ending date based on the number of weekends and holidays, and returns the corresponding value in the form of Date data.

Function WORKDAY.INTL is designed to determine the date equal to the sum of the current date and the duration of the event, and returns the corresponding numeric value in the Excel time code.



Examples of using the functions WORKDAY and WORKDAY.INTL in Excel

Example 1. According to calculations, an employee of a company would need 236 hours of continuous work to complete the project. The project start is 08/18/2018. Determine the date of completion of the project. In the next 30 days there are no holidays, weekends - Saturday and Sunday.

View source data table:

Example 1.

To solve use the formula:

WORKDAY.

Argument Description:

  • B5 - the date the event began (work on the project);
  • B6 / B8 - the number of working days required for the project, provided that the working day lasts 8 hours

Result of calculations:

using the functions.

Automatically determined the number for delivery of the implemented project on the key.



How to calculate the number of working days between dates

To calculate the number of working days or holidays between two dates in Excel, you should use the NETWORKDAYS.INTL function:

.

As you can see, the total number of working days per year and 103 days off (262 + 103 = 365) is only in a non-pending 2019.

Example 2. The course of the subject in the school is designed for 46 classes, which are held on Monday, Wednesday and Friday. The beginning of the study of the subject - 03.09.2018 Upcoming holidays: 09/17/2018, 09/28/2018, 7/10/2018. Determine the date of the last class.

View source data table:

number of working days or holidays.

The formula for calculating:

Example 2.

The result of the calculations:

result.

The last lesson will be December 24, 2018.

Formula for the optimal calculation of number of working days between dates

Example 3. To perform work on time, two options were proposed: to increase the hours in working days to 10 hours per day or enter a six-day working week. The estimated duration of the work is 127 hours. Choose the best option if the project start is 08/20/2018.

View of data table:

Example 3.

To solve, use the following formula:

IF function checks the values returned by the functions WORKDAY.INTL and returns the corresponding result. The longer the date, the longer it will take to complete the project.

The result of the calculations:

WORKDAY.INTL.

That is, in order to complete work faster, it is better to increase the duration of the working day to 10 hours.

Features of using the functions WORKDAY and WORKDAY.INTL in Excel

WORKDAY function has the following syntax:

=WORKDAY(Start_date,Days,[Holidays])

Argument Description:

  • Start_date is a required argument that characterizes the values of the Date format, which is the starting point (the beginning of an event).
  • Days is a required argument that accepts data of a numeric type, which characterizes the number of working days that have passed since the start of any event or preceding it. It is believed that this number does not contain days that are holidays or weekends. For the countdown relative to the date specified as the first argument, this argument should be rendered as a negative number.
  • [Holidays] is an optional argument that accepts Date data format, indicating one or more dates corresponding to holidays. As an argument, an array of cells can be passed.

WORKDAY.INTL function has the following syntax:

=WORKDAY.INTL(Start_date,Days,[Weekend],[Holidays])

Argument Description:

  • Start_date is a required argument that characterizes the start date of any event in the form of data;
  • Days is a required argument in the form of numerical data characterizing the number of days that precede the start of an event (a negative number) or denote a future date (a positive number);
  • [Weekend] is an optional argument that takes one or more Date values that indicate dates of holidays;
  • [Holidays] - optional argument in the form of a string of 7 consecutively recorded values from the range from 0 to 1, characterizing the number of days off and their position by days of the week, or a numerical value from the range from 1 to 17. Examples of some options for writing this argument:
  1. 1 (or not explicitly specified) - weekends are Saturday and Sunday.
  2. 2 - Sunday and Monday.
  3. 3 - Monday and Tuesday (and so on).
  4. 11 - only Monday.
  5. 12 - only Tuesday (and so on).
  6. "0000011" - Saturday and Sunday.
  7. "1000001" - Sunday and Monday (and so on).

Notes:

  1. When using both of the functions in question, it must be borne in mind that Excel supports dates from January 1, 1900 to December 31, 9999. If the first argument or the date of any holiday is presented as a date that does not belong to the range of valid values, both functions will return a code errors #NUM!
  2. If, as a result of adding the date represented by the first argument of any of the functions in question, as well as the number of days specified by the second argument of the function, taking into account a certain number of holidays and weekends, a value outside the valid date range will be returned, the error code #NUM!
  3. If the “Days” argument is represented as a fractional number, the fractional part will be automatically truncated.
  4. The function WORKDAY.INTL returns the error code #NUM! If a number not in the range of valid values or a string containing invalid characters, only units ("1111111") or consisting of less than seven characters was passed as an argument [output].
  5. When entering dates, it is recommended to use not text representations of dates (for example, “February 10, 2017”) as arguments for the functions in question, but functions that return data in format (for example, =DATE(02,10,2017)).

Download examples WORKDAY function to calculate days in Excel

Note: Unlike the WORKDAY function, which provides the ability to specify only the dates of holidays, the WORKDAY.INTL function. Allows you to determine the number of weekends per week and specify which days in the week are the weekend (for example, instead of the usual Saturday and Sunday, you can specify Tuesday and Wednesday).


en ru