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.

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

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

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.

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

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

LOL

(I assume it was intentional)

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


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

Dang, I sincerly do appologize. I thought I only posted the 'at' for what i
had, not what I wanted... the trials and tribulations of my little one
getting up each hour on the hour...

"Rick Rothstein (MVP - VB)" wrote:

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

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

LOL

(I assume it was intentional)

No, I accidentally hit the Enter Key while I had the Control Key
depressed... on Windows Mail (and Outlook Express), that is the short-cut
keystroke for Send The Message.

Rick

I guess we're both having a poor concentration day ;)

"differently. Put this formula...

"

/Insert formula here :)

ha ha



"Rick Rothstein (MVP - VB)" wrote:

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

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 guess we're both having a poor concentration day ;)

"differently. Put this formula...
"
/Insert formula here :)

ha ha

I hope by now that you have seen the follow-up posting I made to the
"missing formula" posting.

Rick

Yeah... I got that one... but you're LIKELY going to want to shoot me when i
tell you it doesn't work... The exactly same answer displays minus the "at"
now... The time is missing one character so for 19:00 it displays 1:00.

"Rick Rothstein (MVP - VB)" wrote:

I guess we're both having a poor concentration day ;)

"differently. Put this formula...
"
/Insert formula here :)

ha ha

I hope by now that you have seen the follow-up posting I made to the
"missing formula" posting.

Rick

Rick hadn't taken account of your change of input format (when you added the
colon after the at). My revised formula did. [Just change the argument for
the MID function for the hours.]
--
David Biddulph

"Titanium" wrote in message
...
Yeah... I got that one... but you're LIKELY going to want to shoot me when
i
tell you it doesn't work... The exactly same answer displays minus the
"at"
now... The time is missing one character so for 19:00 it displays 1:00.

"Rick Rothstein (MVP - VB)" wrote:

I guess we're both having a poor concentration day ;)

"differently. Put this formula...
"
/Insert formula here :)

ha ha

I hope by now that you have seen the follow-up posting I made to the
"missing formula" posting.

Rick

Okay, we have lots of posts now and they are not all sequentially
arranged... so I'll address your question and sum up the two possibilities
in this message...

Did you see David's posting? He points out that you changed the text you
said you have to parse by adding an colon that was not in the original
posting... your text now reads ""20070917 at: 1900" - note the colon after
the word "at". If the colon is a mistake, remove it and use this formula...

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

or, if the colon belongs there, use this formula....

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

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

Rick


"Titanium" wrote in message
...
Yeah... I got that one... but you're LIKELY going to want to shoot me when
i
tell you it doesn't work... The exactly same answer displays minus the
"at"
now... The time is missing one character so for 19:00 it displays 1:00.

"Rick Rothstein (MVP - VB)" wrote:

I guess we're both having a poor concentration day ;)

"differently. Put this formula...
"
/Insert formula here :)

ha ha

I hope by now that you have seen the follow-up posting I made to the
"missing formula" posting.

Rick

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.

These formulas worked perfectly Rick. Thank so much for your help... and a
few laughs inbetween :)

"Rick Rothstein (MVP - VB)" wrote:

Okay, we have lots of posts now and they are not all sequentially
arranged... so I'll address your question and sum up the two possibilities
in this message...

Did you see David's posting? He points out that you changed the text you
said you have to parse by adding an colon that was not in the original
posting... your text now reads ""20070917 at: 1900" - note the colon after
the word "at". If the colon is a mistake, remove it and use this formula...

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

or, if the colon belongs there, use this formula....

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

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

Rick


"Titanium" wrote in message
...
Yeah... I got that one... but you're LIKELY going to want to shoot me when
i
tell you it doesn't work... The exactly same answer displays minus the
"at"
now... The time is missing one character so for 19:00 it displays 1:00.

"Rick Rothstein (MVP - VB)" wrote:

I guess we're both having a poor concentration day ;)

"differently. Put this formula...
"
/Insert formula here :)

ha ha

I hope by now that you have seen the follow-up posting I made to the
"missing formula" posting.

Rick