adding only days

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

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

Adding or subtracting 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

Days, months, and 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 the 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.

adding days months years
Days, months, and years

I hope that helps if you’re using a spreadsheet and you need to add days months or years to a date.

 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>