Date Conversion Formula Needed
The reason why you are seeing 1:00 instead of 19:00 is that you originally
said you had "20070917 at 1900", but now you are using "20070917 at: 1900"
with an extra colon after the "at". If you ask a different question, the
answer is likely to be different!
If you are saying you want it now to read 9/17/07 19:00 (without the at) you
can get that as a text string by the formula
=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2)),"m/d/yy")&MID(A1,13,3)&":"&RIGHT(A1,2)
or you can get it as a proper Excel date and time (with which you could do
further calculations) with the formula
=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID (A1,14,2),RIGHT(A1,2),0)
and format the cell as m/d/yy hh:mm
--
David Biddulph
"Titanium" wrote in message
...
David,
This is what I have:
my original: 20070917 at: 1900
with your formula applied: 9/17/07 at: 1:00
Notice the time is now 1:00. I would like to retain 'most of' the orginal
time, but i want to insert ":' between the hour and minutes. Also, I want
the
at to be no longer displayed. Thanks for all your help.
"David Biddulph" wrote:
=TEXT(DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2)),"m/d/yy")&" at
"&MID(A1,13,2)&":"&RIGHT(A1,2)
--
David Biddulph
"Titanium" wrote in message
...
From a database some info is generated that reads "20070917 at 1900".
I need to be able to generate "9/17/07 at 19:00"
At first I was thinking I could use a macro to do this, but I have a
feeling
there is a much easier formula that can be generated to make the
conversion.
Any help will be much appreciated.
|