How to utilize date add function in Google spreadsheet?

By : Kevin
Source: Stackoverflow.com
Question!

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? Thanks for helping a beginner!

By : Kevin


Answers

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)

By : eymel


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



=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.

By : Olly


This video can help you solving your question :)
By: admin