Add Workdays in Google Sheets

To add or subtract workdays days from a date, respecting holidays but assuming a 7-day week: you can use the WORKDAY.INTL function with an argument that tells it how many days should be subtracted/added as well as which specific holidays to ignore. In our formula below C2 for this example:

=WORKDAY.INTL(A2,B2,"0000000",A7:A8)

Add workdays no weekends in google sheets1

With this formula, you can have a Thursday evening deadline instead of Monday afternoon! This means that all days in the week are treated as working hours. With two holidays  in range A7:A8  and weekends set using the special syntax “0000000“, your work will be done by 5/29/2022, at midnight PST.

Explanation

WORKDAY.INTL allows you to calculate a date in the future or past that respected holidays and weekends with its special codes for designating which days are considered as such, but using masks is more flexible since it allows any day of the week to be designated into being treated like weekend ones & zeros!

You can calculate the number of days required to complete your project in an official workplace with this handy formula. Simply multiply Saturday’s total hours by 0,8 and add it onto Sunday-Friday count!

To add days excluding weekends, simply do the following:

Type this formula =WORKDAY(A2, B2) into the blank cell, and press Enter key to get your answer.

Add workdays no weekends in google sheets1

Tip: To change the start date of your spreadsheet, simply enter “A2” as desired. This will allow you to input future dates into B2 for each day added on top!

We have now reached the end date, which adds 50 business days, excluding weekends.

Note: The number you’re trying to math is a 5-digit one, so in order for it to show up on your screen correctly formatted as date, click Format> Number > Date. See screenshot:

Add workdays no weekends in google sheets1

Now that you have cells containing all of your holiday data, excluding weekends and any other days we want is easy.

Type this formula =WORKDAY(A2, B2, A7:A8) in blank cells and hit the enter to see the result.

Add workdays no weekends in google sheets1

Tip: To create a formula that excludes holidays, use this equation. A2 is the start date, and A7:A8 are days you want to be excluded from your calculation.

Related Functions

  • Google Sheets WORKDAY function
    The Google Sheets WORKDAY function returns a serial number that represents a date that is the indicated number of working days before or after the starting date you specified.The syntax of the WORKDAY function is as below:=WORKDAY(start_date, days, [holidays])…
  • Google Sheets WORKDAY.INTL function
    The Google Sheets WORKDAY.INTL function returns the serial number of the date before or after a specified number of workdays.The syntax of the WORKDAY.INTL function is as below:= WORKDAY.INTL (start_date, days, [weekend], [holidays])…

 

Related Posts

Calculating Average Of The Numbers in Google Sheets
_3 Average numbers in google sheets1

Are you weary of investing a lot of time and effort in manually calculating the average of the numbers by including or excluding 0 and calculating the average of the top 3 scores? Then congratulations because you have just landed ...

Add Row Numbers And Skip Blanks in Google Sheets
Add Row Numbers And Skip Blanks in google sheets1

Do you ever have to input a list of numbers into a spreadsheet in Google Sheets, and some of the cells are blank? It can be difficult to keep track of what number is in which cell when you have ...

Add Months To Date In Google Sheets
Add Months To Date in google sheets1

It is important to adjust time periods when performing financial modeling. In Google Sheets, you can use the MONTH function to add a specific number of months to a date. The MONTH function takes one argument: the number of months ...

Add Leading Zeros in Google Sheets
Add Leading Zeros To Numbers in google sheets1

To pad a number with zeros, you need to add leading spaces and/or carriers. For example, if the original digit contains 2 digits, three additional characters will be added on before it. If there are 3 digits in total, allocating ...

Add Minutes to Time in Google Sheets
Add Minutes to Time in google sheets1

Adding whole hours or decimal minutes to a given time in google sheets is a fairly simple process. You can do this by using the Start_time+TIME(0,minutes,0) and Start_time+minutes/1440 functions. In this add minutes to time guide, we will show you ...

Add Hours to Time in Google Sheets
Add hours to time in google sheets1

Adding whole hours or decimal hours to a given time in Google Sheets is a fairly simple process. You can do this by using the Start_time+TIME(hours,0,0) and Start_time+hours/24 functions. In this add hours to time guide, we will show you ...

Count Cells that are Case Sensitive in Google Sheets
counting the cells that are case sensitive in google sheets1

If you are a frequent user of Google Spreadsheets, you may have come across situations where you needed to add or count the cells that were case-sensitive. If you have done this task manually, I would like to point out ...

Average Last N Values in Google Sheets
Average last 5 values in google sheets1

Average is a valuable function in google sheets that allows you to rapidly compute the average of the past N values in a column. However, you may need to insert new numbers beneath your original information from time to time, ...

Assigning Points based on Late Time in Google Sheets
assign points based on late time in google sheets1

It is shown in this lesson how to utilize the IF function in Excel to allocate points based on the amount of time that has passed. If you intend to pursue along this guide, you may do so by downloading ...

Google Sheets Nexted IF Functions (Statements) Tutorial (15 IF Formulas examples)
google sheets nested if function1

This tutorial will guide you how to use nested Google Sheets IF function  (include multiple If statements in Google Sheets formula) with syntax and provide about 15 nested IF formula examples with the detailed explanation in Google Spreadsheets. Description The ...

Sidebar