58

I believe the issue I am having now should be much easier in MS Excel. However, since my company uses Google Spreadsheet so I have to figure out a way.

Basically, I have a cell that contains a date value like "12/19/11", and I have another cell contains a value like "DT 30". The task assigned to me is to add the value 30(days) to the date, so the result should be "1/19/2012".

I did some trying in Google Spreadsheet, I have two questions. The first is to how to extract the numeric value "30" out of the string "DT 30", the second question is that, there seems to be no date add function built in Google Docs.

Could any experts offer some suggestions?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Kevin
  • 6,711
  • 16
  • 60
  • 107

10 Answers10

56

I like to keep it simple. If A1 holds the date and B1 holds the number of months to add, then

=date(year(A1),month(A1)+B1,day(A1))

would calculate the required result. The same way could be used for days or years

demongolem
  • 9,474
  • 36
  • 90
  • 105
dubi
  • 569
  • 4
  • 2
  • 3
    clever approach. This is the only solution I've seen that allows you to add months to a date – mlathe Oct 03 '14 at 03:12
  • 2
    This is not what OP is asking about. OP clearly needs 30 days, not 1 month, which can be anywhere from 28 to 31 days. Then, this solution is unreliable and won't work for a month of December. For adding X months to date, use `=EDATE(cell; months)`, e.g. `=EDATE(A1; 2)`. – Nowaker Jul 03 '17 at 18:26
  • @Nowaker your EDATE answer is what I wanted, even if the OP didn't. Thanks! – Gary Sheppard Mar 15 '18 at 13:28
43

  1. To extract a numeric value out of your string you can use these 2 functions (Assuming you have your value in cell 'A1'):

    =VALUE(REGEXEXTRACT(A1, "\d+"))

    This will get you a numeric value.

  2. I've found no date add function in docs, but you can convert your date into internal date number and then add days number (If your value is in cell 'A2'):

    =DATEVALUE(A2) + 30

I hope this will help.

brettdj
  • 54,857
  • 16
  • 114
  • 177
Burnash
  • 3,181
  • 2
  • 31
  • 35
  • It's worth noting that the format of the cell must be "Date" of some kind. If it's set to number you won't get anything that makes sense. – jocull Oct 06 '16 at 18:25
42

You can just add the number to the cell with the date.

so if A1: 12/3/2012 and A2: =A1+7 then A2 would display 12/10/2012

kidbrax
  • 2,364
  • 3
  • 30
  • 38
  • 1
    this simply give an Error: "Function ADD parameter 1 expects number values. But '05.09.2019' is a text and cannot be coerced to a number." I have tried to format it both as Date and DateTime. – Jaxx0rr Feb 29 '20 at 08:34
11

You can use the DATE(Year;Month;Day) to make operations on date:

Examples:

=DATE(2013;3;8 + 30) give the result...  7 april 2013 !
=DATE(2013;3 + 15; 8) give the result... 8 june 2014 !

It's very surprising but it works...

Liam
  • 27,717
  • 28
  • 128
  • 190
coutier eric
  • 949
  • 5
  • 18
  • Unreliable solution. For adding X months to date, use `=EDATE(cell; months)`, e.g. `=EDATE(A1; 2)`. – Nowaker Jul 03 '17 at 18:24
  • 2
    Why do you say that it is an unreliable solution ? explanation on https://support.google.com/docs/answer/3092969: DATE will silently recalculate numeric dates which fall outside of valid month or day ranges. For example, DATE(1969,13,1), which specifies the illegal month 13, will create a date of 1/1/1970. Similarly, DATE(1969,1,32), which specifies the non-existent 32nd day of January, will create a date of 2/1/1969. – coutier eric Dec 16 '17 at 15:48
8

The direct use of EDATE(Start_date, months) do the job of ADDDate. Example:

Consider A1 = 20/08/2012 and A2 = 3

=edate(A1; A2)

Would calculate 20/11/2012

PS: dd/mm/yyyy format in my example

Liam
  • 27,717
  • 28
  • 128
  • 190
Ademir Mazer Jr - Nuno
  • 890
  • 18
  • 30
  • 3
    edate adds months, not days, https://support.google.com/drive/bin/static.py?hl=en&topic=25273&page=table.cs&tab=1240285 – kidbrax Dec 15 '12 at 20:44
  • Ooops ... really .. sorry for mistake .. I had used this function in some situation where I need add months and answer here without keep atention on it ... thanks for clarify – Ademir Mazer Jr - Nuno Dec 16 '12 at 17:52
5

As with @kidbrax's answer, you can use the + to add days. To get this to work I had to explicitly declare my cell data as being a date:

A1: =DATE(2014, 03, 28)

A2: =A1+1

Value of A2 is now 29th March 2014

Liam
  • 27,717
  • 28
  • 128
  • 190
RikRak
  • 898
  • 1
  • 7
  • 21
0

Using pretty much the same approach as used by Burnash, for the final result you can use ...

=regexextract(A1,"[0-9]+")+A2

where A1 houses the string with text and number and A2 houses the date of interest

Jason Sturges
  • 15,855
  • 14
  • 59
  • 80
Yogi Anand
  • 434
  • 4
  • 3
0

what's wrong with simple add and convert back?

if A1 is a date field, and A2 hold the number of days to add: =TO_DATE((DATEVALUE(A1)+A2)

eymel
  • 11
0

=TO_DATE(TO_PURE_NUMBER(Insert Date cell, i.e. AM4)+[how many days to add in numbers, e.g. 3 days])

Looks like in practice:

=TO_DATE(TO_PURE_NUMBER(AM4)+3)

Essentially you are converting the date into a pure number and back into a date again.

Liam
  • 27,717
  • 28
  • 128
  • 190
Olly
  • 9
  • 1
0

In a fresh spreadsheet (US locale) with 12/19/11 in A1 and DT 30 in B1 then:

=A1+right(B1,2)

in say C1 returns 1/18/12.

As a string function RIGHT returns Text but that can be coerced into a number when adding. In adding a number to dates unity is treated as one day. Within (very wide) limits, months and even years are adjusted automatically.

pnuts
  • 58,317
  • 11
  • 87
  • 139