Google-sheets – How to apply DATEVALUE to column

google sheetsgoogle-sheets-arrayformula

I have two columns with extracted date values. I want to add those two values together and apply to the whole column. The data looks like this:

AM     AN          AO      AP          AQ 
date    time        year    day value   time value 
Oct 12  14:15:00    2020    #N/A        0.59375

I want to concatenate or join the values of AM and AO, pass to DATEVALUE, and apply to the whole column. Current the formula for AP is:

=DATEVALUE(concatenate(AM2,", ",AO2))

But I cannot apply this using ArrayFormula.

I've tried using CONCAT, JOIN, and & on the two values without success, e.g.

=ArrayFormula(DATEVALUE(AM2:AM & AO2:AO))

Best Answer

You can try the following formula

=ArrayFormula((V2:V5&"-"&MONTH(T2:T5)&"-"&DAY(T2:T5))*1)

(Please adjust ranges to your needs)

If you want to apply for the whole range you can use:

=ArrayFormula(IF(V2:V="",,(V2:V&"-"&MONTH(T2:T)&"-"&DAY(T2:T)*1)))

EDIT

Following OP's request

I actually wanted to keep this in the Datevalue format, e.g. 44116 instead of 10/12/2020.

Please use the following arrayformula

=ArrayFormula(IF(V2:V="",,((V2:V&"-"&MONTH(T2:T)&"-"&DAY(T2:T))*1)))

DO notice the extra parenthesis. They make all the difference.
Using this second formula you get the results as datevalues. If you then want to have them as dates, you can format them from the Format -->Number -->Date menu according to your preferences.

Functions used:

Formula

=ARRAYFORMULA(
   QUERY(
     {
       IFERROR(IF(A2:A = "devops", A2:A, TRIM(REGEXEXTRACT(A2:A, "devops "))),""), 
       B2:B
     }, 
     "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 LABEL SUM(Col2) ''"
   )
 )

copy / paste
=ARRAYFORMULA(QUERY({IFERROR(IF(A2:A = "devops", A2:A, TRIM(REGEXEXTRACT(A2:A, "devops "))),""), B2:B}, "SELECT Col1, SUM(Col2) WHERE Col1 <> '' GROUP BY Col1 LABEL SUM(Col2) ''"))

Screenshot

Google-sheets – Find slope/intercept of last 30 values entered

The command

=query(A7:F, "select A, F where F is not null order by A desc limit 30")

returns the last 30 values where F is nonempty. If this command is placed, say, in G1, then it fills the array G1:H30, to which you can then refer:

=DATEVALUE(TODAY() + ((108.36297798)-(INTERCEPT(H1:H30, G1:G30)))/(SLOPE(H1:H30, G1:G30)))

In principle, one can avoid creating a helper array and have just one formula returning the result. This requires replacing the references to A with

=query(A7:F, "select A where F is not null order by A desc limit 30")

and the references to F with

=query(A7:F, "select F where F is not null order by A desc limit 30")

The result is kind of long and scary but it works.

=DATEVALUE(TODAY() + ((108.36297798)-(INTERCEPT(query(A7:F, "select F where F is not null order by A desc limit 30"), query(A7:F, "select A where F is not null order by A desc limit 30"))))/(SLOPE(query(A7:F, "select F where F is not null order by A desc limit 30"), query(A7:F, "select A where F is not null order by A desc limit 30"))))

Best Answer

Related Solutions

Google-sheets – Add the numbers in rows where a string contains a given word

Formula

Screenshot

Google-sheets – Find slope/intercept of last 30 values entered

Related Topic