How to utilize date add function in Google spreadsheet

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[google-sheets] How to utilize date add function in Google spreadsheet?

Examples related to google-sheets

Examples related to formula