Add & Subtract Days in Google Sheets (With Examples)

Managing time-sensitive data in Google Sheets frequently requires precise date arithmetic, specifically the ability to add or subtract days from a designated starting point. This process is surprisingly straightforward because spreadsheets, including Google Sheets, treat dates not as text but as internal serial numbers. This underlying structure allows us to manipulate dates efficiently using standard mathematical operators like plus (+) and minus (-).

The immediate benefit of the serial number system is that you can adjust a date by a fixed number of calendar days using exceptionally simple formulas. The following examples illustrate how to modify a date contained within cell A2 by 10 days, covering both forward and backward calculations.

#add 10 days to the date in cell A2
=A2+10

#subtract 10 days from the date in cell A2
=A2-10

While simple addition is perfect for general calendar adjustments, many operational requirements—such as calculating project deadlines, delivery timelines, or invoice due dates—must strictly exclude weekends (Saturdays and Sundays). For these specific scenarios, where calculations must rely solely on standard workdays, Google Sheets provides a dedicated and robust function.

To accurately add or subtract workdays while automatically skipping non-working days, we utilize the powerful WORKDAY function. It is crucial to remember that when subtracting workdays using this function, the number of days must be input as a negative integer.

#add 10 workdays to the date in cell A2
=workday(A2, 10)

#subtract 10 workdays from the date in cell A2
=workday(A2, -10)

The following sections provide detailed, step-by-step illustrations for implementing both basic date arithmetic, suitable for calendar-based offsets, and the advanced workday calculations, which are essential for professional scheduling within your spreadsheets.

Introduction to Date Arithmetic in Google Sheets

A foundational understanding of how Google Sheets manages dates is indispensable for ensuring accurate calculations. Dates are not treated as conventional text strings; rather, they are processed as sequential numerical values. This system, known as the serial number system, dictates that day one begins on December 30, 1899, with each subsequent day incrementing the integer value by one. This internal mechanism is precisely what permits the direct use of addition and subtraction operators to reliably modify date values.

When a date is entered into a cell, the spreadsheet automatically converts it into a corresponding large integer. If you then add or subtract an integer (e.g., 20 or 5), you are simply manipulating this underlying serial value. The spreadsheet subsequently translates the resulting serial number back into a recognizable date format. This method is the fastest and simplest approach when the inclusion of all days—including weekends and holidays—is acceptable, such as when calculating the expiration date for a service contract 90 days from the start date.

A critical step after performing any date calculation is verifying the cell formatting. If a calculation yields a result that looks like a large, arbitrary number (e.g., 45300), it means the cell is displaying the raw serial value instead of the formatted date. To resolve this, you must apply the correct format: navigate to the ‘Format’ menu, select ‘Number,’ and then choose the appropriate date presentation, such as ‘Date’ or ‘Date time.’

Practical Application: Step-by-Step Calendar Day Adjustments (Example 1)

Let us examine a common scenario that illustrates the use of simple addition and subtraction for adjusting dates by a fixed number of calendar days. Imagine a project management context where we need to quickly determine both a projected completion date (10 days forward) and a crucial pre-submission review date (10 days backward) relative to a set of start dates.

Our initial dataset includes several project start dates listed in Column A, which will serve as the reference point for all subsequent calculations:

To calculate the date exactly 10 days after the reference date in cell A2, we simply input the addition formula =A2+10 into cell B2. This formula can then be dragged down across the entire range, swiftly calculating all future dates based purely on the calendar day count, without regard for weekends or holidays.

Conversely, to determine the date 10 days preceding the start date, we employ the subtraction operator, using the formula: =A2-10. This basic arithmetic provides a reliable and effective solution for defining offset periods when the inclusion of weekends is acceptable and necessary for the required calculation.

Advanced Date Manipulation: Using the WORKDAY Function

The limitation of standard date arithmetic becomes apparent when calculations must align strictly with a standard five-day business week (typically Monday through Friday). For critical business functions—such as setting official project milestones, managing payroll cycles, or accurately forecasting shipment delivery times—it is essential to systematically exclude all non-working days. This requirement makes the WORKDAY function an indispensable tool for advanced spreadsheet users.

The WORKDAY function is designed to calculate a resulting date either in the future or the past based exclusively on the number of specified working days, automatically bypassing standard weekend days (Saturday and Sunday). Its fundamental syntax is highly versatile, allowing for optional exclusions, and is defined as: WORKDAY(start_date, num_days, [holidays]).

To utilize this powerful function effectively, it is necessary to understand the purpose of each argument:

• start_date: This compulsory argument specifies the initial reference date from which the calculation of working days commences.

• num_days: This required integer value specifies the exact number of workdays to be added or subtracted. A positive integer moves the date forward in time, while a negative integer calculates a date backward.

• [holidays]: This argument is optional but highly recommended for accuracy. It requires a range of cells containing dates that represent non-working official holidays, ensuring they are also excluded from the resulting calculation alongside weekends.

For international or non-standard operational contexts where the customary weekend structure differs (e.g., Friday and Saturday, or only Sunday), Google Sheets provides an alternative: the WORKDAY.INTL function. This advanced version allows the user to fully customize the definition of the weekend using specific numerical codes, thereby maximizing flexibility across different regional requirements.

Implementing WORKDAY: Adjusting Dates by Business Days (Example 2)

In dynamic operational planning, calculating deadlines that fall only on business days is crucial for maintaining realistic schedules. For instance, determining a precise ship date exactly 10 working days after an order date requires the WORKDAY function to ensure that the projected timeline correctly excludes all non-operational weekend days, providing an accurate estimate for logistics and customer service.

Using the same foundational list of start dates referenced in Example 1, we apply the WORKDAY function to calculate the date 10 business days into the future. The formula required for this forward calculation is concise and efficient:

=WORKDAY(A2, 10)

This calculation automatically evaluates the calendar, skipping any intervening Saturdays or Sundays, guaranteeing that the final resulting date is a true, operational business day. This is essential for accurate scheduling where weekends cannot be counted as part of the lead time.

To perform reverse scheduling—for example, calculating the last possible start date 10 working days before a final deadline—we must move backward in time. This is achieved by simply preceding the number of days within the function with a negative sign. The resulting formula, =WORKDAY(A2, -10), is vital for defining preparatory timeline steps and ensuring resources are allocated correctly before a final target date.

Key Considerations and Troubleshooting

While date calculations in Google Sheets are highly reliable, users often encounter specific issues or require advanced controls to manage nuanced scheduling needs. Understanding these common points of failure and advanced features is key to ensuring continuous data accuracy and reliable scheduling outcomes.

To maximize the precision and reliability of your date arithmetic, spreadsheet experts recommend adhering to the following best practices:

• Handling Holidays: If your operational schedule includes specific, non-standard holidays (outside of standard weekends), it is essential to utilize the optional third argument of the WORKDAY function. Compile all official holiday dates into a separate, dedicated range and reference that range within the formula to ensure those non-working days are correctly skipped.

• Regional Weekends: If your organization does not observe the standard Saturday/Sunday weekend, you must transition from the basic WORKDAY function to the advanced WORKDAY.INTL function. This version allows you to specify custom weekend patterns using a simple numerical code (ranging from 1 to 17), accommodating regional differences like Friday/Saturday weekends.

• Formatting Errors: The most common error is a visual one: when a date calculation returns a large integer instead of a formatted date. Always verify that the cell format is set correctly by navigating to Format > Number > Date to ensure the serial number is translated into a human-readable date.

• Data Type Consistency: Ensure that the initial date reference cell (e.g., A2) is definitively recognized by Google Sheets as a date data type, rather than a text string. If the starting value is stored as text, fundamental arithmetic operations will invariably fail, returning an error.

Mastering these two distinct methods—simple arithmetic for general calendar offsets and the robust WORKDAY function for professional business day scheduling—provides the necessary foundation for executing precise and comprehensive date management in any complex data analysis or project planning environment.

Additional Resources for Date Functions

Related:

To further solidify your expertise in date manipulation within Google Sheets, the following related tutorials offer explanations on other essential operations, including calculating the exact time difference between two dates and properly handling time stamps in various formats.