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.

=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.

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.

Rick


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=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.

Did you post the wrong formula Rick? - that one seems to be exactly the same
as David's

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Rick Rothstein (MVP - VB)" wrote in
message ...
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.

Rick


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
=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.

Did you post the wrong formula Rick? - that one seems to be exactly
the same as David's

You and I must have noticed my posting error at about the same time as the
timestamp on your post and on my correction are the same.

Rick

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

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

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

<<Damn that Ctrl+Enter key combination

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...

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID (A1,13,2),RIGHT(A1,2),0)

in your cell and then format the cell as Date using the "3/14/01 13:30" item
from the Type list.

Rick


"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

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.

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.

I used the second of the two here and it worked perfectly. Thank you so much.
Again, sorry for the mis-worded question at first. My daughter had me up
every hour on the hour last night...

"David Biddulph" wrote:

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.