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.

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

=DATE(YEAR(B6)+C6,MONTH(B6)+D6,DAY(B6)+E6)

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.

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.

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.