Date Conversion Formula Needed

T

Titanium

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

David Biddulph

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

Rick Rothstein \(MVP - VB\)

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
 
R

Rick Rothstein \(MVP - VB\)

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.
Click to expand...

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
 
S

Sandy Mann

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
R

Rick Rothstein \(MVP - VB\)

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

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
 
T

Titanium

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

Titanium

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

Rick Rothstein \(MVP - VB\)

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

Rick Rothstein \(MVP - VB\)

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

Sandy Mann

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...
Click to expand...

LOL

(I assume it was intentional)

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
T

Titanium

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

Rick Rothstein \(MVP - VB\)

you only see 1:00 when, on my system, the formula I posted
LOL

(I assume it was intentional)
Click to expand...

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
 
D

David Biddulph

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
 
R

Rick Rothstein \(MVP - VB\)

I guess we're both having a poor concentration day ;)
"differently. Put this formula...
/Insert formula here :)

ha ha
Click to expand...

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

Rick
 
T

Titanium

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

David Biddulph

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

Rick Rothstein \(MVP - VB\)

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
 
T

Titanium

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