1

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))
3
  • Welcome to Web Applications. What are the data types of column AM and AO values? Are the values of column AM text or date? Oct 12, 2020 at 18:38
  • @Rubén I believe these are plain text.
    – JP Cronin
    Oct 12, 2020 at 18:58
  • Please be review that. What is shown in the formula bar when you select AM2? Have you tried to use ISTEXT, ISDATE functions? Oct 12, 2020 at 20:03

2 Answers 2

0

You can try the following formula

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

enter image description here

(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:

0
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.