What is Excel WORKDAY Function?

Excel WORKDAY function is one of the functions that come under Excel Date and Time Functions. It was introduced in Excel 2007 and is available in all other subsequent versions of Excel.

It is used to add or subtract a given number of days to or from a specified date and thereby returns a date with the specified number of working days added to or subtracted from the start date.

How Excel WORKDAY Function Works?

Excel WORKDAY function takes the start_date, days, and [holidays] arguments and returns the date that comes after or before the specified number of working days.

WORKDAY function is programmed to skip weekends which by default is Saturday and Sunday.

The number of workdays specified can either be a positive or negative number. If it is a positive number, Excel WORKDAY function calculates the date that comes after the start date, and if it is a negative value, it calculates the date that comes before the start date.

Syntax

The syntax for WORKDAY function is:

=WORKDAY(start_date, days, [holidays])

And the Input Arguments are as follows:

· start_date – This is the initial date from which to start counting the number of workdays

· days – This is the number of working days to be added to or subtracted from the start_date. These number of days excludes weekends (Saturday or Sunday), and can take either a positive or negative value.

· [holidays] – This is optional. It specifies an array of dates that are excluded from being counted as working days. If a holiday date falls on a weekend (i.e Saturday or Sunday), it will be counted as weekend only.

When Excel WORKDAY Function is Used?

Excel WORKDAY function is used to calculate any date that excludes non-working days, after a given number of working days has been added to or subtracted from it.

For example, if an invoice was booked today, and it will take 20 days to redeem it, then WORKDAY function can be used to calculate the expected due date to redeem the invoice.

WORKDAY function works best in calculating employees payment due date, a debtor’s payback date, or the expected delivery date for an order, or completion date for a project that takes into account working and non-working days.

Additional Notes

· The start_date and [holidays] arguments can be entered as:

– cell references containing the dates

– a date result from other formulas

– date stored as text

– any date entered as text (i.e. in double quotes)

· The WORKDAY function does not count weekends (Saturday and Sunday). You can however, customize which days of the week you want as weekend with Excel WORKDAY.INTL function.

· It is risky to enter dates as text, as Excel may misinterpret them. This is because the dates systems or date interpretations vary according to the settings on your computer.

How to Use Excel WORKDAY Function: Functions and Examples

To understand how to use Excel WORKDAY function, let us take a look at some examples.

In the above worksheet, WORKDAY function is used in two ways.

1. Get the Due Date (Excludes only Weekends)

Excel WORKDAY function returns the due date when the start date and number of working days are specified. Automatically, the function does not count Saturdays and Sundays as they have been programmed to be non-working days.

So here, we use the formula:

=WORKDAY(A7,B7)

=WORKDAY(“01-Jan-2021”, -180)

In the first data row, we count -180 days backwards from 01-Jan-2021 and it gives us 24-Apr-2020. Since the number of working days specified is a negative number, it will count the dates backwards.

Note that in this formula, the holiday is not specified, which means that Excel Workday function counts the working days including the holidays and excludes all the weekends, based on the number of working days specified in column B.

2. Get the Due Date after Specified Number of Working Days (Excludes Weekends and Holidays)

In the earlier example, we used a WORKDAY formula that calculated the working days and excluded only the weekends (Saturdays and Sundays). The holidays were not excluded and so would not give us the correct working date.

In this case, we use the third argument that specifies the holidays that will not be counted, using the formula:

=WORKDAY(A7,B7, Holiday)

=WORKDAY(A7,B7, $F$7:$F$17)

From the above worksheet example, a start date of 01-Feb-21 after 10 working days gives a result of 15-Feb-21. This excludes the weekends (Saturdays and Sundays) and the specified array of dates for holidays.

But you can see that there is no holiday in the month of February.

On the other hand, the start date of 01-Mar-21, after 60 working days gives a result of 28-May-21. Excel WORKDAY function excluded not only the weekends, but also the holidays on 02-Apr-21, 05-April-21, 12-May-21 and 13-May-21.

It should be noted that, the cell reference name “Holiday” used in the formula is for the cell range F7:F17 that contains the holiday dates.

Common Excel WORKDAY Function Errors

There are common errors you get when using Excel WORKDAY function which are: