How to apply DATEVALUE to column

Question

I have extracted date values in AM2:AO:

	AM	AN	AO	AP	AQ
1	date	time	year	date value	time value
2	Oct 12	14:15:00	2020	44116	0.59375

I am trying to create an array formula in AP2. For each row in AM2:AO, CONCATENATE (or JOIN) the values in columns AM and AO and then pass that result into a DATEVALUE function.

Current formula in AP2 that works for a single formula is:

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

I cannot apply this formula to the entire range using ARRAYFORMULA.

I've tried using CONCAT, JOIN, &, all without success.

Example of a formula that doesn't work:

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

Answer 1

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:

• ArrayFormula
• IF
• MONTH
• DAY

Answer 2

Formula

=ARRAYFORMULA(IFERROR(DATEVALUE(AM2:AM&" "&AO2:AO)))

Explanation

The formula is based on the assumption that Column AM is either formatted as "Plain text" or the values themselves are explicitly text strings such as ="Oct 12" or 'Oct 12). This assumption is based on the fact that your single-row formula =DATEVALUE(CONCATENATE(AM2,", ",AO2)) succeeded. The formatting of column AO doesn't matter.

   YOUR ATTEMPT

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

Your attempt using the ARRAYFORMULA function was extremely close and your error was that you did not include a delimiter between the day and the year (you did have a delimiter in your single-row formula).

So, the following array formula would have worked:

=ARRAYFORMULA(DATEVALUE(AM2:AM & " " & AO2:AO))

DATEVALUE is somewhat forgiving with respect to the actual delimiter used and a space, comma, combined comma and space, all work in my locale. There are likely other options as well.

After that correction, all that remains is to optionally use IFERROR to deal with #VALUE! errors generated if a row in AM2:AM and/or AO2:AO doesn't contain a valid value.

=ARRAYFORMULA(IFERROR(DATEVALUE(AM2:AM&" "&AO2:AO)))

alternatively

=IFERROR(ARRAYFORMULA(DATEVALUE(AM2:AM&" "&AO2:AO)))