83

I'm using a Google Spreadsheet to perform calculations and I'd like to do some simple date math. For example,

TODAY() add 7 days.

I don't see any functions to do it and some quick stabs in the dark didn't pan out.

Does anyone know if this is possible?

Improve this question

6 Answers 6

Reset to default
105

You can add days to a date in a Google spreadsheet like this:

DATE(2010, 12, 01) + 5

Works with TODAY() as well:

TODAY() + 7
Improve this answer
5
  • How can you do this but take only workdays into account? (exclude weekends?)
    – Alfie
    Feb 21, 2013 at 21:24
  • 4
    @Alfie Why don't you post that as a question?
    – Jacob Jan Tuinstra
    Mar 31, 2014 at 19:58
  • 1
    Note that you may have to change the format of the result cell to "Date," otherwise you'll just get a random-looking large number.
    – Nathan Arthur
    Oct 9, 2015 at 20:17
  • @Alfie - google NETWORKDAYS
    – Adam Nofsinger
    Nov 29, 2015 at 0:53
  • @Alfie I think you need the WORKDAY function
    – Chris
    Jul 15, 2020 at 20:56
13

Here's another way to add days to a date that builds on the previous answer.

Let's say you have this in Cell A1

DATE(2010, 12, 01)

You can add 5 days to it in Cell A2 using the DATEVALUE command like this

DATEVALUE(A1) +5

Improve this answer
3
  • 1
    Using DateValue on content that is already a date (which the output of date is) is redundant.
    – user135384
    Dec 28, 2016 at 22:46
  • Not if you want to be able to adjust multiple cells using a single entry.
    – Mike
    Dec 28, 2016 at 23:17
  • In my example, making an adjustment in A1 will also adjust A2. A good system to use if you have a time sensitive process where the end date is dependent on the start date.
    – Mike
    Dec 28, 2016 at 23:19
4

The way I do it is:
place the date in row A1, in row A2 using the formula do = A1 + [number of Days]

Eg.: 

A1 [11/12/2018]
A2 [=A1+3]
A2 [November/15/2018]

(I format the date with an easy to interpret format (select row > Format > Number > More Formats > More date and time formats)

Improve this answer
2

Internally both Google and Excel store timestamps as a day + fraction.

So Jan 3 2018 + 3 = Jan 6 2018.

You can see this if you display a date as a number.

Timestamps are stored as a decimal fraction of a day. 6 a.m. is .25 etc.

Formatting changes the way a number is interpreted. In Sheets there are a raft of general building blocks for constructing various special formats. e.g. you can do elapsed time in just minutes, in hours and minutes, in days, hours and minutes depending on how you want the format to display. This can sometimes bite you. I wanted to subtract off lunch breaks in minutes from the result of H:MM start time and H:MM end time. The first time I didn't say 'minutes' and it was interpreted as days so I got this large negative number.

Improve this answer
1

I am used to doing this in Open Office. Being UK I used the dd/mm/yyyy format, which Google does not recognise. So if I enter 2/10/2019, adding 1 produces 2/11/2019. Choose Oct/2 as format and then you can just do =[original cell] + 1 to get Oct/3.

Improve this answer
0

To add a number of workdays to a date (excluding weekends), use WORKDAY(start_date, num_days, [holidays])

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.