Workday Function: How To Determine Work Finish Date In Excel

Excel WORKDAY formula – this time we will learn about how to determine the work end date.

Actually, to find out the end date or the end date of work, we can just add the start date of work with the number of hours worked in units of days.

It’s just that if this calculation of the end of work excludes weekends or on Saturdays and Sundays the workers are off, then we will be a little confused to determine the number of weekend days (Saturday & Sunday) during the working period.

Not to mention if during this working period there are also several work days off other than weekends.

To determine the completion date of this work, Microsoft Excel provides the WORKDAY function .

In Microsoft Excel the Workday function is the opposite of the Networkdays function. If the Networkdays function returns the number of workdays in a given date range, the Workday function returns the date the work was completed.

If in the Networkday function the main parameter is the date of completion of the work, in the Workday function the main parameter used is the length of the work in days.

In the Workday formula, weekends are always Saturday and Sunday. If you use Microsoft Excel 2010 and above, this weekend parameter can also be set in more detail if you use WORKDAY.INTL function which has the same function.

To be clear, let’s talk more about how to use the Workday and Workday.Intl functions along with examples of their use.

Excel WORKDAY Function

The Excel WORKDAY function is used to return the serial number of the date before or after a specified number of working days excluding weekends and other dates specified as working holidays.

When using the workday formula, weekends are Saturdays and Sundays. If you intend to set another day as a working day off then use the Workday.Intl formula available in Microsoft Excel 2010 and above.

If you don’t want to ignore weekends and other holidays, you can simply add up the start date of work with the length of work in days. If the length of work is in months you can also use the Edate function.

How to Use the Workday Formula

To use the Workday function in excel formulas, the syntax or how to write it is as follows:

Code:
WORKDAY(Start_Date;Number_Days; [Holiday_Day])

Information

• Start_Date

Filled with work start date to be calculated.

• Number of days

Fill in the number of days the work is completed.

• [Holiday]

Is a data range or array constant that contains a date value that indicates the serial number of the date that will be excluded as workdays. This argument is optional so it can be left blank if there are no work holidays that will be ignored.

Example of Using the Workday Excel Formula

For an example of using the Workday formula in excel, please look at the following picture:

Code:

=WORKDAY(C2;D2)

In the first example of the excel formula above, the Workday function is used to find out the date of completion of work, where the start of work was on April 3, 2017 with 15 days of work.

In this formula, if you add the date directly to the number of working days, then the work completion date should be April 28, 2017. In the Workday formula, Saturdays and Sundays are not counted as effective working days, so the completion date for this work is April 28. 2017.

Code:

=WORKDAY(C3;D3;A2:A4)

In this second formula, apart from ignoring Saturday and Sunday, we also include other holidays, namely those in the A2:A4 range, so 15 working days after April 3, 2017 is April 26, 2017.

In the 3rd and 4th formulas:

Code:

=WORKDAY(C4;D4)

=WORKDAY(C5;D5;A2:A4)

The method of calculating the date also applies as before, only in these two excel formulas the value of the Amount_Day argument is filled with a minus number or less than 0 so that the date is counted backwards before the start date of work.

One of the shortcomings of this Workday function is the Weekend setting or weekends that are only Saturday and Sunday. If the calculation date that we use turns out to be a working holiday that is only used on Friday, for example, this function will not work as expected. For this reason, starting with the 2010 version of Microsoft Excel , more advanced functions have been added to replace this Workday function. The function in question is the Workday.Intl function .

Excel WORKDAY.INTL function

The WORKDAY.INTL function has basically the same function as the WORKDAY function , in that it is used to return the date before or after a certain day period, ignoring weekends and other holidays. It’s just that in this Workday.Intl function we can more freely determine which days we specify as weekends.

The WORKDAY.INTL function is only available for Microsoft Excel 2010 version and above.

How to Use the Workday.Intl Formula

The parameters or arguments of the Workday.Intl function are the same as the Workday functions, except that in this function there is an option to add the Weekend_Argument which is used to determine which days we will define as weekends or work holidays in each week.

The writing or syntax of the function is as follows:

Code:

WORKDAY.INTL(Start_Date;Number_Days; [Weekend]; [Holiday_Holidays])

Information:

• Start_Date

Filled with the start date of work to be calculated.

• Number of days

To be inputted with the number of days the work is completed.

• [Weekend]

Filled with weekend code that will be ignored from weekdays. This argument can be left blank (not used). If left blank, Workday.Intl will use Saturday and Sunday as weekends.

• [Holiday]

Is a data range or array constant that contains one or more serial numbers of work holidays. This argument is optional so it can be left blank if there are no work holidays that will be ignored.

There are 2 kinds of weekend codes in this function, in the form of numbers 1-17 or text string codes that indicate which days we will set as working days off in the week.

The numeric code for the Weekend parameter in the Workday.Intl function is as follows:

In addition to using the numeric code above, we can also set the weekend parameter with a seven-character text string in the form of a combination of the text numbers 1 and 0 representing Monday-Sunday. The number 1 represents holidays while the number 0 represents work days .

For example for weekends or holidays Saturday and Sunday the string code used is 0000011 . As for those who come to work only Monday, Tuesday and Thursday (Wednesday, Friday, Saturday and Sunday off) then the String code is 0010111 .

Example of Using the Workday.Intl excel Formula

An example of using the Workday.Intl formula is as follows:

Code:

=WORKDAY.INTL(C2;D2)

In this first formula the workday.Intl function will work exactly like the Workday formula where this formula calculates the work finish date starting on April 3 for 15 Days ignoring Saturday and Sunday. In this formula argument Weekends and Holidays us empty.

Code:

=WORKDAY.INTL(C2;D2;11)

In the second excel formula, we specify the Weekend argument with the number 11 which means that weekends fall only on Sundays, while the Holiday_Holiday argument we leave blank.

Code:

=WORKDAY.INTL(C2;D2;”0000001″;A2:A4)

In the 3rd formula, we use the string code to set the Weekend argument . We enter this string code enclosed by double quotes (“…”) to indicate that the data we enter is text.

Code:

=WORKDAY.INTL(C2;D2;;A2:A4)

For the last Workday.Intl example, we don’t use the Weekend_Parameter, which means that we set weekends by default, which are Saturdays and Sundays. In this formula, what needs to be considered is that if you use the Day_Holiday parameter without the Weekend_Parameter, then the writing method is like the example above, where the Day_holiday argument is written after 2 argument separators (;;) or (,,) if using the English setting.

Thus our discussion this time about how to determine the work completion date using the Workday and Workday.Intl functions.

If you find it useful, don’t hesitate to share it on the social media that you use so that more people can benefit. Greetings Excel Class.