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))
  • Welcome to Web Applications. What are the data types of column AM and AO values? Are the values of column AM text or date?
    – Rubén
    Oct 12 '20 at 18:38
  • @Rubén I believe these are plain text.
    – JP Cronin
    Oct 12 '20 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?
    – Rubén
    Oct 12 '20 at 20:03

You can try the following formula


enter image description here

(Please adjust ranges to your needs)

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



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


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:

  • Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. Oct 12 '20 at 17:58
  • I actually wanted to keep this in the Datevalue format, e.g. 44116 instead of 10/12/2020.
    – JP Cronin
    Oct 12 '20 at 18:54
  • Please check the updated answer. Oct 12 '20 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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