 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / September 2007Date Conversion Formula Needed
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)  SignatureDavid Biddulph > 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" [quoted text clipped - 5 lines] > > 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 > =TEXT(DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2)),"m/d/yy")&" at > "&MID(A1,13,2)&":"&RIGHT(A1,2) [quoted text clipped - 7 lines] >> >> 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... [quoted text clipped - 6 lines] > 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. > > You can shorten your formula and save a couple of function calls by using > > virtually the same string functions this way instead... [quoted text clipped - 13 lines] > > 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... > Rick, > [quoted text clipped - 28 lines] >> >> 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) SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > 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 [quoted text clipped - 5 lines] > > (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 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... > 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 [quoted text clipped - 34 lines] > >> > >> Rick I guess we're both having a poor concentration day ;) "differently. Put this formula... >" /Insert formula here :) ha ha > 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 [quoted text clipped - 34 lines] > >> > >> Rick >I guess we're both having a poor concentration day ;) > [quoted text clipped - 3 lines] > > 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. > >I guess we're both having a poor concentration day ;) > > [quoted text clipped - 8 lines] > > 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.] SignatureDavid Biddulph > Yeah... I got that one... but you're LIKELY going to want to shoot me when > i [quoted text clipped - 14 lines] >> >> 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 > Yeah... I got that one... but you're LIKELY going to want to shoot me when > i [quoted text clipped - 14 lines] >> >> Rick These formulas worked perfectly Rick. Thank so much for your help... and a few laughs inbetween :) > 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 [quoted text clipped - 34 lines] > >> > >> 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 > Rick, > [quoted text clipped - 28 lines] >> >> Rick Did you post the wrong formula Rick? - that one seems to be exactly the same as David's SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > You can shorten your formula and save a couple of function calls by using > virtually the same string functions this way instead... [quoted text clipped - 20 lines] >>> >>> 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. > =TEXT(DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2)),"m/d/yy")&" at > "&MID(A1,13,2)&":"&RIGHT(A1,2) [quoted text clipped - 7 lines] > > > > 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 SignatureDavid Biddulph > David, > [quoted text clipped - 19 lines] >> > >> > 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... > 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" [quoted text clipped - 31 lines] > >> > > >> > Any help will be much appreciated. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.