9

I need to extract a 'date string' as separate columns representing date/time data.

 | A                        | B                 | C
-+--------------------------+-------------------+-----------------
1| Datetime String          | Date Value        | Time Value
2| Sat 09 Sep 2017 20:00 PM | =DATEVALUE(A2)    | =TIMEVALUE(A2)

The above functions DATEVALUE and TIMEVALUE return errors. I'm at a bit of a loss at the moment.

Charles Goodwin
  • 6,402
  • 3
  • 34
  • 63
  • Is the value in A2 a string or a number with a number format? – moggi Sep 04 '17 at 17:13
  • However looking through the code it seems that your format does not correspond to a valid date or time number format anyway. So based on a quick reading of the DATEVALUE function's source code there is no chance to recognize the format as a date or time anyway. – moggi Sep 04 '17 at 17:17

3 Answers3

11

It looks like the date string is fixed width, so use this formula for cell B2, which builds a date string in YYYY-MMM-DD format and then converts with DATEVALUE.

=DATEVALUE(MID(A2,12,4) & "-" & MID(A2,8,3) & "-" & MID(A2,5,2))

The formula for cell C2 only needs to extract the time, which is already in a suitable format.

=TIMEVALUE(MID(A2,17,5))

Then go to Format -> Cells, and format B2 as a date and C2 as a time.

Jim K
  • 12,824
  • 2
  • 22
  • 51
2

You could try =MID(A2,1,FIND(":",A2)-4) for the Date Value, and =MID(A2,FIND(":",A2)-2,8) for the Time Value.

John B. Walugembe
  • 589
  • 4
  • 12
  • I haven't tried this yet but upvoted it to help you on your way with reputation. I ticked the other answer because it 1) worked and 2) was more thorough (included the tip on formatting the cells). – Charles Goodwin Sep 05 '17 at 07:30
0

Just in case anyone else comes across Cater Allen's new export format: 25Dec2020 (i.e. DDMMMYYYY) that isn't recognised during CSV import:

=DATEVALUE(NUMBERVALUE(MID(A2,6,4))&"-"&MID(A2,3,3)&"-"&NUMBERVALUE(MID(A2,1,2))) will turn it into a proper date that YNAB can recognise.

Thanks for the question and other answers that helped me get there neatly.

Tim Abell
  • 11,186
  • 8
  • 79
  • 110