The WORKDAY function in Google Sheets is useful if you want to get a date that is the N working days in the future or the past.
Meaning, the WORKDAY function returns a date that is either after or before a certain number of days from the provided start date.
Table of Contents
The rules for using the WORKDAY function in Google Sheets are as follows:
- The WORKDAY function excludes weekends (Saturday and Sunday) so If you wish to customize weekends, we need to use the WORKDAY.INTL function instead.
- The function will return the #NUM error when the arguments provided result in an invalid date.
- If the provided second argument, which is the days, is not an integer, it will be truncated.
- The function will return the #VALUE error when the dates arguments are not valid dates and the days provided are non-numeric.
- It is recommended that for the dates arguments, they should be entered as either reference to cells containing dates or dates returned from formulas.
Let’s take an example.
Seth owns an online store and caters to international deliveries. He mentions to his clients that for orders coming overseas, the package should arrive 7 working days from the order date, excluding holidays.
He created a database of the orders for him to track easily and so that he can do necessary follow-ups to the third-party courier should there be any unforeseen delays in the delivery.
His simple database looks like below:
With the help of the WORKDAY function, Seth was able to identify each order’s delivery date. See his table below:
Not only that it saved him from tedious work, but the WORKDAY function is also convenient for him since it considers the holidays.
Watch out for a more advanced tutorial and examples on how you can use the WORKDAY function in the coming weeks. Be sure to subscribe to be notified.
Perfect! Let’s begin getting to know more about our WORKDAY function in Google Sheets.
The Anatomy of the WORKDAY Function
So the syntax (the way we write) of the WORKDAY function is as follows:
=WORKDAY(start_date, days, [holidays])
Let’s dissect this thing and understand what each of these terms means:
- = the equal sign is just how we start any function in Google Sheets. It is how Google Sheets understand that we are asking it to either do computation or use a function.
- WORKDAY() this is our WORKDAY function. It returns a date N working days in the future or past.
- start_date is the date from which to start.
- days is the number of workdays to be added to start_date. If we enter a positive value, it will give us a future date, while a negative value will yield a past date.
- [holidays] is an optional argument. This is the list of dates that should be considered non-work days.
A Real Example of Using WORKDAY Function
Let’s take a look at the simple database that Seth created below to see how the WORKDAY function is used in Google Sheets.
Notice in Seth’s table that he was able to identify the delivery date using the WORKDAY function.
The delivery dates in column D are 7 days from the order dates in column C. This excludes the holidays, which were listed in column G.
Let’s take a look at the example in the second row. He just passed three arguments to our function. The first one is the start_date, which tells the WORKDAY function where to begin counting. In this case, it is the order date of the item.
The second argument is integer 7, which tells the WORKDAY function the number of working days it needs to add from the start_date.
Please note that the WORKDAY function only counts working days. Hence, Saturday and Sunday are not included.
Lastly, the third argument is an optional list of holidays that also Seth didn’t want the WORKDAY function to include in counting.
In this case, he put all the holidays for the year 2021 in column G from the second row until the 34th row.
The return value of the WORKDAY function in our example above is the date ‘6/23/2021’. This means that it’s the 7th working day from the order date ‘6/11/2021’ excluding holidays.
You can quickly verify this by checking the calendar like the example below:
Note that June 21 is one of the holidays and is listed in column G.
What if you wanted to know the 7th working day before start_date?
Well, that’s easy. Instead of 7, pass -7 to our WORKDAY function and it will return to you the 7th working day before the provided start_date.
In this case, the WORKDAY function returned the date ‘6/2/2021’, you can verify using the calendar below:
You may make a copy of the spreadsheet using the link I have attached below.
How to Use WORKDAY Function in Google Sheets
- Click on any cell to make it the active cell. For this guide, I will be selecting D2, where I want to show the result.
- Next, type the equal sign ‘=‘ to begin the function and then follow it with the name of the function, which is our ‘workday‘ (or ‘WORKDAY‘, not case sensitive like our other functions).
- Type open parenthesis ‘(‘ or simply hit Tab key to let you use that function.
- Now the exciting part! Let’s give our function its only argument, the start_date. Click the cell where our order date is located. In this case, click on cell C2. Alternatively, you may type in ‘C2’.
- To let the Google sheet know that we’re done typing our first argument, we should now type in the delimiter or the character that separates each argument on a function. In this case, type comma ‘,’.
- Type in our second argument, which is the days. In this case, we will use ‘7’ to denote 7 working days. Type in ‘7’ and follow it with another comma ‘,’.
- Now, pass the last but optional argument, which is the [holidays]. Since we put the list of holidays in range G2:G24, type in ‘$G$2:$G$24’. The dollar ‘$’ character means we are making the range an absolute reference since we are copying the formula down to the remaining rows later.
- Finally, hit your Enter or Tab key. Cell D2 will now show you the return value of the WORKDAY function, or the 7th working day from the start date provided.
- Copy the formula down to cells D3 to D16.
That’s pretty much it. You can now use the WORKDAY function in Google Sheets together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier.