Answered by:
how do I get Excel to recognize DD Mmm YYYY@HHMM as a valid date/time?
Question

I get flat files from a legacy system that have date/times in the format DD Mmm YYYY@HHMM. E.g.,
02 Apr 2015@1050 06 Apr 2015@1046 08 Apr 2015@0945 08 Apr 2015@1147 08 Apr 2015@1325
How would I get excel to natively recognize this as a valid date/time format?
I can cobble together a formula to do the conversion, but since I work with this date/time format all the time I'd rather teach Excel to recognize it as a valid date/time, if possible.
Thanks
Christian Bahnsen
Answers

You'll have to use either formulas or VBA code to convert those values to valid date/time values after the fact. I don't think there's a way to make Excel recognize them automatically when opening a text file.
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Proposed as answer by ryguy72 Wednesday, October 21, 2015 9:58 PM
 Marked as answer by Edward8520Microsoft contingent staff Thursday, October 29, 2015 3:23 AM

Hans,
Thanks for your reply and that may in fact be the answer, but do you mind if we wait a bit before flagging it as such just in case someone else comes up with a solution?
As far as a formula to do a conversion, this is what I came up with (assuming a value like 02 Apr 2015@1050 is in cell A1):
=DATEVALUE(LEFT(A1,11)) + TIMEVALUE(CONCATENATE(MID(A1,13,2),":",RIGHT(A1,2)))
I'm open to improvements on this formula ...
Thanks again
Chris
 Proposed as answer by Edward8520Microsoft contingent staff Monday, October 26, 2015 8:56 AM
 Marked as answer by Edward8520Microsoft contingent staff Thursday, October 29, 2015 3:23 AM
All replies

You'll have to use either formulas or VBA code to convert those values to valid date/time values after the fact. I don't think there's a way to make Excel recognize them automatically when opening a text file.
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Proposed as answer by ryguy72 Wednesday, October 21, 2015 9:58 PM
 Marked as answer by Edward8520Microsoft contingent staff Thursday, October 29, 2015 3:23 AM

Hans,
Thanks for your reply and that may in fact be the answer, but do you mind if we wait a bit before flagging it as such just in case someone else comes up with a solution?
As far as a formula to do a conversion, this is what I came up with (assuming a value like 02 Apr 2015@1050 is in cell A1):
=DATEVALUE(LEFT(A1,11)) + TIMEVALUE(CONCATENATE(MID(A1,13,2),":",RIGHT(A1,2)))
I'm open to improvements on this formula ...
Thanks again
Chris
 Proposed as answer by Edward8520Microsoft contingent staff Monday, October 26, 2015 8:56 AM
 Marked as answer by Edward8520Microsoft contingent staff Thursday, October 29, 2015 3:23 AM