Calculate with Weekdays Only in Google Sheets (An Easy Guide)

In our simple guide today, we look at how we can find and calculate with weekdays only in Google Sheets. Thankfully, the application makes it easy for us users by providing some specialized date functions.

Table of Contents

Calculate with Weekdays Only in Google Sheets

1. How to Autofill Weekdays Only in Google Sheets (Exclude Weekends)

Google Sheets presents us with a couple of ways to generate and autofill dates for weekdays or business days. This can be done with two aptly named functions called WEEKDAY and WORKDAY.

But before we get into it, let’s try to update the typical date format to show the name of the corresponding days. Note that while this is only for presentation purposes, the method can prove useful for any user. But feel free to skip ahead to the actual methods of this article.

To easily format the date to also give us the day name, simply navigate to the “Custom date and time” option from the Format tab. Format > Number > Custom date and time

Once there, apply the following format:

This should update our date to something like this without changing the underlying value:

Learn More: How to Format Date in Google Sheets (3 Easy Ways)

With the formatting out of the way, let’s dive into our methods.

I. Using the WEEKDAY Function

The crux of our first approach is the WEEKDAY function. The function returns a number that represents a day of the week. By default, 1 is represented by Sunday, which means that 7 is Saturday.

WEEKDAY(date, [type])

We can set the type of week we want by updating the [type] field:

1 (default): The week starts from Sunday with value 1.
2: The week starts from Monday with value 1.
3: The week starts from Monday with value 0 and Sunday as 6.

Since we are following the Saturday and Sunday weekends, our type will be 2.

Now, to set our condition, we must use the IF function. And since our formula depends on a starting date (for us it’s in cell B3), our formula looks like this:

=B3+IF(WEEKDAY(B3)>5,3,1)

Next, dragging the fill handle down to automatically generate days that are weekdays only (Monday to Friday):

II. Using the WORKDAY.INTL Function

We can get similar results with the WORKDAY function as well, or to be more precise, the WORKDAY.INTL function. The WORKDAY.INTL function works better as it has a separate [weekend] field to accommodate the user’s choice of weekends.

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

This time, the [weekend] field will cover our condition to exclude weekends in our weekday generation. This field works by taking an integer input of 1 to 7 to represent the weekend combinations:

1 (default): Saturday and Sunday
2: Sunday and Monday
3: Monday and Tuesday
4: Tuesday and Wednesday
5: Wednesday and Thursday
6: Thursday and Friday
7: Friday and Saturday

For our example, we will leave this field as 1 as we are counting weekdays from Monday to Friday.

The formula:

=WORKDAY.INTL($B$3,ROW(A1),1)

Formula Breakdown:

Inside the WORKDAY.INTL formula, we have three fields to talk about.

$B$3: This is our start date. We have locked the cell reference as the rest of the row will continuously point to this date as we move down the row to generate the weekdays.
ROW(A1): This acts as our day number generator, with ROW(A1) generating 1. It adds to the start date as we move down the row generating weekdays.
1: This is our weekend type. The 1 represents a Saturday/Sunday weekend.

Note: We can also set our weekends or days to skip in string form. This field takes seven digits as strings in the form of 1s and 0s. 1 represents the day to skip and the string starts from a Monday. E.g., “0000011” means that Saturday and Sunday are weekends.

Automatically generating the formula for the rest of the rows:

2. Count Days Excluding Weekends and Holidays (Weekdays Only)

Where WORKDAY.INTL generates the number of the day of a corresponding date, the NETWORKDAY.INTL function counts the number of workdays between two dates in Google Sheets. That with customizable weekends.

We can see the similarity in the function’s syntax:

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

So, to count the number of only weekdays (taking Saturday/Sunday as the weekend) between two dates in Google Sheets, our formula will be:

=NETWORKDAYS.INTL(C2,C3,1)

Note: You can also use the TODAY function in place of the end date to calculate the number of weekdays from a date to the current date in Google Sheets.

On top of having a similar [weekend] field, the function also has the [holiday] field, the same as WORKDAY.INTL. You can add a single holiday date or multiple of them. What we like to do is list all the holidays in a separate table and reference the cell range in the [holiday] field of the function.

=NETWORKDAYS.INTL(C2,C3,1,C8:C13)

Some holidays naturally fall on the weekends which are automatically excluded from the count.

Note that this exclusion of holidays works the same way in the WORKDAY.INTL function.

Learn More: Calculate Days Between Dates in Google Sheets (5 Easy Ways)

3. Get Weekday Name from Date

It is quite simple to get the name of a date from a date in Google Sheets as it is already stored in the underlying value.

In the first method of this article, we have already shown you how to generate the name of the day from a given date with formatting.

Similarly, we can also extract the day name by using simple TEXT function formatting. The text format code to only extract the name of the day is “DDDD”. Our formula:

=TEXT(B3,"DDDD")

However, if you want a more customizable day name output, we have to get a little creative with one of the functions that we have already discussed, the WEEKDAY function.

By now, we all know that the WEEKDAY function returns a specific number representing a day of the week. We will use that number to generate our customized output.

Our formula:

=CHOOSE(WEEKDAY(B3),"Weekend","MON","TUE","WED","THURS","FRI","Weekend")

The CHOOSE function takes the number generated by the WEEKDAY function, starting from 1 for Sunday, and outputs a text corresponding to the result. Since we have seven days, we must include seven results.

Learn More: Find Weekday Name from a Date in Google Sheets (3 Easy Ways)

Final Words

Using and calculating only weekdays in Google Sheets may seem like a complicated task at first. But with special date functions like WEEKDAY and WORKDAY, the task becomes that much more intuitive and easy to understand.

Feel free to leave any queries or advice you might have in the comments section below. Or have a look at some of our other date-related articles that are sure to interest you.