Workday Function In Google Sheets: Everything You Need To Know

Workday Function In Google Sheets: Everything You Need To Know
Reading Time: 6 minutes

Quick guide
How to use the WORKDAY function in Google Sheets:
↠ To count 12 workdays forward from 25 Sep:
=WORKDAY(“25/09/2022”,12) 
This will return 11 Oct as the answer
↠ To count 12 workdays backward from 25 Sep:
=WORKDAY(“25/09/2022”, -12)
This will give 8 Sep as the result

The WORKDAY function in Google Sheets is a function used to calculate the work date that is the indicated number of working days before or after a date. This function automatically excludes the weekends as holidays unless specified otherwise. In addition, the function has an optional argument for holidays which can be provided so that the holidays are not counted as working days

We can use the WORKDAY function in Google Sheets to calculate the invoice due dates, expected delivery times, the number of days of work performed, and project completion dates taking into account only the working days.

The WORKDAY function in Google Sheets is a useful tool and we are going to learn some of the useful things we can do with it with examples.

Formula of the WORKDAY function in Google Sheets

The syntax of the WORKDAY function in Google Sheets is as follows:

=WORKDAY(start_date, num_days, [holidays])

The arguments of the syntax are as given below:

  • start_date – the date from which to begin counting
  • num_days – the number of working days before or after the start_date. A positive value counts forward from the start_date while a negative value counts backwards.
  • holidays – an optional list of one or more dates to exclude from the working calendar. The list can either be a range of cells that contain the dates or an array of serial number that represent the dates

How to use the WORKDAY function in Google Sheets

In this section of the tutorial, we’re going to learn the different ways we can use the WORKDAY function in Google Sheets for a variety of purposes.

Let’s begin with a simple example.

You can make a copy of the spreadsheet used in this tutorial with all the examples. Get it here.

Calculate the project completion date using the WORKDAY function in Google Sheets

Suppose we have a project beginning on September 25, 2022 which will take 10 days to complete. We can use the WORKDAY function in Google Sheets to compute the days on which it’s expected to be completed after excluding the weekend.

The formula will be as shown below.

=WORKDAY(DATE(2022,09,25), 10)

Note: We need to embed the DATE function within the WORKDAY function or enclosed the date under quotation marks as otherwise the function will not recognise the date and will return an error.

This will return the date as Friday, October 7, 2022.

WORKDAY function in Google Sheets with the DATE function

We can omit the DATE function by enclosing the date by a pair of quotes.

WORKDAY function with quoted date

Another method is to reference cells that contain the start date and the number of days.

For the above example, the formula will be as follows:

=WORKDAY(A3,B3)

Where A3 is the cell reference for the start_date and B3 contains the value of the number of days.

WORKDAY function in Google Sheets with cell reference

If there are multiple projects with different starting dates and the expected number of days required, we can calculate the end dates by using the same WORKDAY function. All we need to do is to enter the start dates and the number of days in the corresponding cells underneath and drag the formula to return the results for all the projects.

Estimated completion of multiple projects

Using the WORKDAY function in Google Sheets to calculate project completion dates excluding holidays

The above examples did not consider the other non-working days besides the weekends and so the completion dates returned could be earlier than they’d actually take.

We can rectify this by specifying the holidays in that time period.

Let us consider it with an example.

Let us assume that a project is to be completed in 30 days and is scheduled to start on 10 December 2022.

What we need to do is list the holidays in a separate column and reference the range of the cells as shown below.

Listing the holidays in new columns

In order to calculate the estimated completion date of the project taking into account the holidays and the weekends, we can use the formula shown below.

=WORKDAY(A2, B2, E2:E7)

Where A2 and B2 are the cells that contain the start date and the number of days respectively, and E2:E7 is the range of cells with the holidays.

Upon entering the formula, we get the following result: Monday, 23 January, 2023.

Estimated date of project completion with the holidays taken into account

Calculating the start date of a project based on the delivery date using the WORKDAY function

We can use the WORKDAY function in Google Sheets to determine the date on which a project should be begun considering the date on which the project has to be completed and the estimated number of days it’d take to complete.

Suppose that we have a project which would take about 20 days to complete and is to be completed by 20 December 2022.

The formula will be as given below.

=WORKDAY(B1,B2)

Since we’re counting backwards from the project deadline, we use a negative number for the number of days.

Upon entering the formula, we’ll get the result as shown below.

Counting the workdays backward

Using the WORKDAY function in Google Sheets to fetch all the working days

We can use the WORKDAY function in Google Sheets to generate all the working days in a given period.

Enter the first day of the month in a cell (in this case, A1). Then under that cell, enter the following formula:

=WORKDAY(A1, 1)

The nun_days argument is 1 since we want to get all the working days–the project completion day being essentially 1.

Enter the formula and drag the formula cell till the days of the month is exhausted.

Listing all the working days of a month using the WORKDAY function in Google Sheets

As we can see from the screenshot, only the working days are returned with the weekends omitted.

Throughout this tutorial, we have only considered the examples assuming that our working days are from Monday through Friday. But what about cases where Saturday is a working day and the weekend is not Saturday and Sunday?

Fortunately, there is another function called the WORKDAY.INTL. We can use this function to customise the weekend and the number of days off in a week. Let us learn about it.

How to use the WORKDAY.INTL function in Google Sheets

Let us assume that the working days at a company is from Monday through Saturday so that there is only one day weekend instead of the usual two. In this case, we need to use the WORKDAY.INTL function instead of  the WORKDAY function which assumes Saturday and Sunday as weekend.

Syntax of the WORKDAY.INTL function in Google Sheets

=WORKDAY.INTL(start_date, num_days, [weekend], [holidays])

The weekend argument is an optional parameter and defaults to 1 if not specified. 1 is for Saturday and Sunday as weekends. The rest of the arguments is the same as for the WORKDAY function.

Let us consider an example.

Suppose we have to complete a project which would require 30 working days scheduled to begin on October 3.

Using the WORKDAY.INTL function in Google Sheets

To calculate the estimated date of completion of the project using the WORKDAY.INTL function in Google Sheets, we can use the following formula.

=WORKDAY.INTL(B1,B2,11)

Here B1 is the cell that contains the start date and B2 the number of days. We use 11 for the weekend argument as that is the code for Sunday being the only weekend.

For more information on the WORKDAY.INTL function and the number codes for different weekends, visit this page.

WORKDAY.INTL function in Google Sheets

There are a bunch of other things we can use the WORKDAY function in Google Sheets for (as also the WORKDAY.INTL function) but they are for another day.

Conclusion

The WORKDAY function in Google Sheets is a very handy tool and can be used for a variety of purposes, as we have learned. I hope this article has been helpful.

See also

Working with the WORKDAY function in Google Sheets is just one of the many things you can do with Google Sheets for. We have a number of tutorials that cover tricks and tips in Google Sheets. You can discover them here.

Here are some related articles you may be interested in:

https://blog.tryamigo.com/how-to-use-the-weekday-function-in-google-sheets/

https://blog.tryamigo.com/how-to-insert-current-date-in-google-sheets/

https://blog.tryamigo.com/how-to-add-a-date-picker-in-google-sheets/