adding only days

Google Sheets – Add or subtract days months or years to a date

If you’re using Google Sheets and you want to add or subtract days, months, or years to a date, we’ll step through how to do that.

Add or Subtract Just Days

Working with just days is simple because of the way dates are stored in Google Sheets. All you do is take the starting day and then you add the value to it. We’re going to add the value that’s in E5 to B5 and it steps that value forward by nine days.

adding only days
Adding only days

Video explanation

Add or Subtract Days, Months, or Years

Things get a bit trickier if you want to add years, months, or days because dates are stored just as serial numbers. They are incremented only by days and then Google Sheets figures out what that total is in days, months, and years. In order to add months or years instead of days, you have to wrap this in the DATE function as shown in this linked file used to create this post. The DATE function tells Google Sheets that we are looking at a date and that date has three values. Start your formula with an = sign like you always do. Your inputs are the month, and day, and year that we’re starting with. The formula to add the amounts in row 6 of the image is


This is going to take B6 and add the number of years, months, and days to it that are in this table. This adds a year but it also added 11 months and 27 days so it almost added two entire years. That’s why it’s 2018. The function is smart enough to know the different numbers of days in each month and if you go over 12 months, it increments the year by one. If copy and paste the formula down the column, you see that it’s working in all these situations. Just for fun, we’re gonna take a negative 46 in the last example to perform subtraction. That works as well. It took you back in time to 1932.

adding days months years
Days, months, and years

I hope that helps if you’re using a spreadsheet to add or subtract days, months, or years. For another post about working with dates, learn how to sort them as they can be quite picky.

 Live examples in Sheets

Go to this spreadsheet for examples of adding and subtracing days, months, or years that you can study and use anywhere you would like.

Using a Plugin to Work with Dates

If you are doing a lot of work with dates, you can a plugin called Power Tools to, among other things, split dates and times into separate cells.

The Split Date and Time Menu
Split Date and Time Menu

Disclosure: This is an independently owned website that sometimes receives compensation from the company's mentioned products. Prolific Oaktree tests each product, and any opinions expressed here are our own.

One thought on “Google Sheets – Add or subtract days months or years to a date

Leave a Reply