0

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 at 18:38
  • @Rubén I believe these are plain text. – JP Cronin Oct 12 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 at 20:03
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:

| improve this answer | |
  • 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. – marikamitsos Oct 12 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 at 18:54
  • Please check the updated answer. – marikamitsos Oct 12 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.