none
how do I get Excel to recognize DD Mmm YYYY@HHMM as a valid date/time? RRS feed

  • 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

    Wednesday, October 21, 2015 1:05 PM

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)

    Wednesday, October 21, 2015 2:25 PM
  • 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

    Thursday, October 22, 2015 12:30 PM

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)

    Wednesday, October 21, 2015 2:25 PM
  • 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

    Thursday, October 22, 2015 12:30 PM