How to utilize date add function in Google spreads

问题:

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?

回答1:

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.

回答2:

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

回答3:

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

回答4:

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

回答5:

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

回答6:

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

回答7:

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

回答8:

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)

回答9:

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

回答10:

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.