Here, I’ll show you how to add days to date in Google Sheets. You might want to add a certain number of days up to a delivery date or add a security period of a few days to a given date.
Whatever your purpose, Google Sheets add days to date is really easy.
In this tutorial I will show you two ways to add days to a date in Google Sheets:
- Adding a date by value or from a cell reference
- Adding a date using the DATE function
In the end, we will also show you a neat trick to add days to the current date, depending on the date the worksheet is opened.
This Article Covers:
Note: This tutorial also works exactly the same if you are looking to add days to date in Excel.
Two Ways to Add Days to a Date in Google Sheets
To understand how to add days to a date, we are going to consider the following list of dates:
Now let us take a look at the two ways to add a given number of days to these dates. Just watch out for one common issue. In some cases, Google Sheets reads a date as text. It’s a common issue, and there’s a pretty easy way to resolve it. If you have trouble with this, you’ll need to convert text to date in Google Sheets.
Option 1: Adding Days to a Date by Value in Google Sheets
Let’s say we want to add 10 days to each of the dates. In that case, all we need to do is use the formula:
=date+number_of_days
So if you want to add 10 days to the date “06/5/2021”, your formula would be:
=“06/05/2021”+10
Don’t forget to format the cell to the DATE format, or you will end up getting just a number, like this:
To convert the cell into the DATE format, navigate to Format->Number->Date
Now coming back to our original dataset. Let us look at the steps needed to add 10 to all the dates in column A:
- In the first cell (B2), type the formula: =A2+10, followed by the return key
- Drag the fill handle down to copy the formula to all the other cells of column B
All your dates should now have 10 days added to them.
As you can see, all we needed to do was simply add the number of days, like we do in normal addition.
Let us take a look at another scenario where we need to add different numbers of days to each date, as shown below:
As you can see, we have each cell in column B tell us how many days we want to add to the corresponding date in column A. Let us see the steps needed to get our results in this case:
- In the first cell (C2), type the formula: =A2+B2, followed by the return key.
- Drag the fill handle down to copy the formula to all the other cells of column C.
That’s all there is to it! All we needed to do was add a reference to the cell containing the number of days to be added!
Explanation of the Formula
As you have surely noticed, we used a really simple addition formula to add the days to the dates, as if they were normal integers. The reason for this is that dates, in Google Sheets, are inherently represented as serial numbers. The serial starts from 1, which actually represents the date December 31, 1899. For each day after this date, the serial keeps increasing by 1.
So the date May 06, 2021 is actually serial 44322, because it is exactly 44,322 days after December 31, 1899. When you add days to a date in Google Sheets, it essentially adds this number to the serial number it represents. So when you add 10 days to 06/05/2021, what’s really happening is you are adding to the serial 44322.
44322+10 = 44332
This serial represents the date 16/05/2021, which is 10 days away from the original date!
What if I get a Serial Number as the Sum Instead of Date?
There may be cases where, instead of getting your result in a DATE format, you end up getting a serial number. This might happen if the cell containing your result is in a Number or Text format.
To solve this problem, you can easily convert the serial number to the DATE format by navigating to Format->Number->Date.
If you want the date to be displayed in a different format, then select Format->Number-> More Formats->More Date and Time Formats, and then select your required format from the ‘Custom Date and Time Formats’ window that appears.
Option 2: Adding Days to a Date using the DATE Function in Google Sheets
Another way in which you can add days to a date in Google Sheets is by using the DATE function. This method will be more complicated than the simple addition method explained in the previous section, but there might be cases where you may find this useful.
The method mainly involves breaking down your original date into month, day, and year and then adding the number of days you want to add to the number of days in the original date.
In other words, if you have a date, say, 06/05/2021 in cell A2, and you want to add 10 days to the date, the formula you will use is:
=DATE(YEAR(A2),MONTH(A2),DAY(A2)+10)
Let’s break this down:
- The DATE function is used to specify a value in the DATE format. Thus, whenever you use this formula, your result will automatically get converted to the DATE format without you having to explicitly format it from the main menu.
- The date function accepts 3 integer parameters: the year number, the month number, and the day number. So the date 06/05/2021 is represented as DATE(2021,05,06).
- The YEAR function extracts the year value of the date in cell A2. So YEAR(A2) in this case will return 2021.
- The MONTH function extracts the month value of the date in cell A2. So MONTH(A2) in this case will return 5.
- Similarly, the DAY function extracts the day value of the date in cell A2. So DAY(A2) in this case will return 6. When you add 10 to this result, you get 6+10=16.
- Finally, the DATE function combines these three parameters (2021,5,16) and returns the date 16/05/2021!
You might be curious as to why we need the MONTH and YEAR functions when we only want to add 10 days to the original date. The reason for this is that the format of the DATE function requires all three parameters to perform the calculation.
You can use this method to even add months and years to your date. So, if you want to add, say, 1 year, 2 months, and 10 days to a date, you can easily use this formula by adding 1 to the first parameter, 2 to the second and 10 to the third. Your formula would then become:
=DATE(YEAR(A2)+1,MONTH(A2)+2,DAY(A2)+10)
Here’s what you would get as the result:
Adding Days to the Current Date in Google Sheets
Finally, let us take a look at a situation where you want to always add a specific number of days, for example, 10 days, to the current date, depending on when the Google Sheets file is opened.
The TODAY function comes quite in handy when you need to use the current date, as per the local date and time settings on the user’s computer.
So every time the user opens the file, the date changes to the current local date of the user in the DATE format.
If you want to always calculate the date 10 days after the current local date, you can add the number of days to the TODAY function as follows:
=TODAY()+10
The TODAY function is dynamic or ‘volatile’, so it recalculates automatically every time.
Challenges when Adding Days to Date in Google Sheets
Adding extra days to a date in Google Sheets may seem straightforward, but there are a few challenges to be aware of:
- Data Format: One challenge is ensuring that your date is in the correct format. Google Sheets often interprets dates differently based on your locale settings. You may need to use functions like DATE or DATEVALUE to make sure your date is recognized correctly before adding days.
- Handling Weekends: If you want to add a specific number of business days (excluding weekends), you’ll need to implement logic to skip Saturdays and Sundays. This requires creating a formula that checks the day of the week and increments the date accordingly.
- Accounting for Holidays: If your calculation involves skipping holidays, the challenge becomes more complex. You’ll need to maintain a list of holidays and incorporate logic to check if the result falls on a holiday and adjust accordingly. This typically requires custom scripting in Google Apps Script.
- Negative Days: Handling negative days (i.e., subtracting days) can sometimes lead to unexpected results. Be careful when subtracting days from dates, as it might not always work as intuitively as adding days.
- Leap Years: When working with dates that cross leap years, you must account for the extra day in February. Adding or subtracting days that span February 29th requires special consideration.
- Errors with Invalid Dates: If your calculation results in an invalid date, such as February 30th or November 31st, Google Sheets might return an error. You’ll need to implement error handling to ensure your formula behaves correctly in such cases.
- Time Zones: If your date calculations involve time zones, you need to be mindful of potential discrepancies when adding days. Consider whether your date should be in a specific time zone or if adjustments need to be made.
To overcome these challenges effectively, it’s essential to have a good understanding of date functions and logical expressions in Google Sheets.
Additionally, if your calculations involve complex date manipulations, consider using Google Apps Script to create custom functions that can handle your specific requirements.
Final Notes
In this tutorial I showed you two ways in which you can add days to a date in Google Sheets. We also showed you how to add days to the current date, depending on when the user opens the file.
Note that all the above methods work the same way if you want to subtract days from a date too. All you need to do is replace the ‘+’ operation with a ‘-‘.
