How to utilize date add function in Google spreadsheet?
Google SheetsFormulaGoogle Sheets Problem Overview
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?
Google Sheets Solutions
Solution 1  Google Sheets
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
Solution 2  Google Sheets

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.

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.
Solution 3  Google Sheets
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
Solution 4  Google Sheets
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...
Solution 5  Google Sheets
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
Solution 6  Google Sheets
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
Solution 7  Google Sheets
Using pretty much the same approach as used by Burnash, for the final result you can use ...
=regexextract(A1,"[09]+")+A2
where A1 houses the string with text and number and A2 houses the date of interest
Solution 8  Google Sheets
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)
Solution 9  Google Sheets
=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.
Solution 10  Google Sheets
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.