45

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?

63

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
  • How can you do this but take only workdays into account? (exclude weekends?) – Alfie Feb 21 '13 at 21:24
  • 3
    @Alfie Why don't you post that as a question? – Jacob Jan Tuinstra Mar 31 '14 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 '15 at 20:17
  • @Alfie - google NETWORKDAYS – Adam Nofsinger Nov 29 '15 at 0:53
6

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

  • 1
    Using DateValue on content that is already a date (which the output of date is) is redundant. – user135384 Dec 28 '16 at 22:46
  • Not if you want to be able to adjust multiple cells using a single entry. – Mike Dec 28 '16 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 '16 at 23:19
2

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)

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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