I'm not sure why you only see 1:00 when, on my system, the formula I posted
shows 19:00 for the time part. That notwithstanding, you now want something
different than you originally asked for (your original post showed the word
"at" in your desired result) which means we can approach the problem
differently. Put this formula...


"Titanium" wrote in message
...
Rick,

My original: 20070917 at: 1900
Results with your formula: 9/17/07 at 1:00

I would like to remove the 'at' as well, the time is not displaying
correctly. I would like the time to output 19:00. I'm not sure where the
1:00. Could i ask also that you explain your formula a bit. I'm having
some
difficulties understanding how it reads. Thanks for all your help.


"Rick Rothstein (MVP - VB)" wrote:

You can shorten your formula and save a couple of function calls by
using
virtually the same string functions this way instead...

=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2)),"m/d/yy")&" at
"&MID(A1,13,2)&":"&RIGHT(A1,2)

However, this requires a separate cell to implement and I kind of get
the
impression the OP wants to do the text parsing within the same cell
(sort
of like a "custom format"). That, of course, would require a macro
solution.

Of course it would help if I had pasted my formula into my message
instead
of your formula. This is formula I meant to include in my post...

=--MID(A1,5,2)&"/"&--MID(A1,7,2)&"/"&MID(A1,3,2)&MID(A1,9,6)&":"&RIGHT(A1,2)

Rick