 |
 | 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 / General Excel Questions / December 2007Date format from yyyy-mm-dd-hh
I have list with dates eg 11/24/2007 9:45 pm I need to transform those dates to 2007-11-24 9:45pm. I have tried the cell format and the data,text to colum ymd and still have not been able to fix it. I so some one had add extra column and ad a function. I just don't know how to make the function convertion. I realy apreciate any input with this issue. If you only need to format the existing dates, Try this Custom Number format: Select the range of dates From the Excel Main Menu: <format><cells><number tab> Category: Custom Type: yyyy-mm-dd h:mmam/pm Click [OK] If you want to use a formula in another column then...with a date in A1 This formula displays that date in the format you requested: B1: =TEXT(A1,"yyyy-mm-dd h:mmam/pm") Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) >I have list with dates eg 11/24/2007 9:45 pm I need to transform those >dates [quoted text clipped - 3 lines] > column and ad a function. I just don't know how to make the function > convertion. I realy apreciate any input with this issue. I tried but the date does not change I still have 5/4/2007 7:23:52 rather than 2007-12-30. I tried the custom format and other formats but nothing changes stings but it seems that no changes take effect. When I enter the function you gave it displays the same information. Any hints! > If you only need to format the existing dates, > Try this Custom Number format: [quoted text clipped - 28 lines] > > column and ad a function. I just don't know how to make the function > > convertion. I realy apreciate any input with this issue. If 5/4/2007 7:23:52 does not display as 2007-05-04 7:23AM that suggests that the source cell may have leading spaces and is TEXT, not an actual DATE. To test....try this A1: (the date to be converted) B1: =TEXT(TRIM(A1),"yyyy-mm-dd h:mmam/pm") Does that display properly? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) >I tried but the date does not change I still have 5/4/2007 7:23:52 > rather than 2007-12-30. I tried the custom format and other formats but [quoted text clipped - 34 lines] >> > column and ad a function. I just don't know how to make the function >> > convertion. I realy apreciate any input with this issue. Ron, thank you for the response I did the test and still display the same date when I enter the =TEXT(TRIM(V2),"yyyy-mm-dd h:mmam/pm") you suggested. In column v2 displays 11/8/2007 4:43:05 PM and on column w2 displays 11/8/2007 4:43:05 PM. > If 5/4/2007 7:23:52 does not display as 2007-05-04 7:23AM > that suggests that the source cell may have leading spaces [quoted text clipped - 51 lines] > >> > column and ad a function. I just don't know how to make the function > >> > convertion. I realy apreciate any input with this issue. OK...Let's troubleshoot. First we'll make sure the method *can* work: 1) Open a new workbook 2) Create the scenario by ENTERING the source values and formulas (not copy/pasting). Do the formulas behave? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > Ron, thank you for the response > I did the test and still display the same date when I enter the [quoted text clipped - 60 lines] >> >> > column and ad a function. I just don't know how to make the function >> >> > convertion. I realy apreciate any input with this issue. No the formula did not work. > OK...Let's troubleshoot. > [quoted text clipped - 76 lines] > >> >> > column and ad a function. I just don't know how to make the function > >> >> > convertion. I realy apreciate any input with this issue. Hmmm.....I hate to ask...but....you ARE using Microsoft Excel, right? Let's keep checking. Try this formula (assuming the source "date" is in cell A1): =ISNUMBER(A1) It should return: TRUE. If it doesn't, there's a problem with the source value and you'll need to post the exact contents of that cell so we can hopefully see what's wrong. Alternatively, you might want to post that new workbook to one of the free file hosting services so we can see what you're working with: http://www.flypicture.com/ http://cjoint.com/index.php http://www.savefile.com/index.php -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > No the formula did not work. > [quoted text clipped - 82 lines] >> >> >> > function >> >> >> > convertion. I realy apreciate any input with this issue. Yes, I am working with Excel 2003. It return FALSE. I uploade the data on the website named test.xls I thank you so much for your assistance in trying to resolve this issue. > Hmmm.....I hate to ask...but....you ARE using Microsoft Excel, right? > [quoted text clipped - 108 lines] > >> >> >> > function > >> >> >> > convertion. I realy apreciate any input with this issue. Hi, Lumi None of those sites allows you to search for files. You'll need to post the link to the file location. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > Yes, I am working with Excel 2003. It return FALSE. I uploade the data on > the [quoted text clipped - 117 lines] >> >> >> >> > function >> >> >> >> > convertion. I realy apreciate any input with this issue. direct link http://www.freefilehosting.net/download/39h8c Html code<a href="http://www.freefilehosting.net/files/39h8c">test.xls</a> direct link [URL="http://www.freefilehosting.net/files/39h8c"]test.xls[/URL] > Hmmm.....I hate to ask...but....you ARE using Microsoft Excel, right? > [quoted text clipped - 108 lines] > >> >> >> > function > >> >> >> > convertion. I realy apreciate any input with this issue. Thanks for posting the link......That's exactly what was needed! The Col_A data is NOT numeric, although it is forced to be right-aligned. To actually convert it... 1)Select the "dates" 2)From the Excel Main Menu <data><text-to-columns>....Click [Finish] Note, though, the forumula in B2 (on my computer) is returning the correct value either way: 2007-11-08 4:43PM But, in case it wasn't on your computer, the text-to-columns method should correct that. Does it? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > direct link http://www.freefilehosting.net/download/39h8c > Html code<a href="http://www.freefilehosting.net/files/39h8c">test.xls</a> [quoted text clipped - 117 lines] >> >> >> >> > function >> >> >> >> > convertion. I realy apreciate any input with this issue. Unfortunately, I had try this and it does not change it. You think is any other setting? As part of the test, if I enter 11-29-2007 it does recognize it as number, but enter 11/29/2007 does not recognize it as number. so I wonder if the / are cousing problems and don't know how to convert this since I am exporting this from another source. > Thanks for posting the link......That's exactly what was needed! > [quoted text clipped - 142 lines] > >> >> >> >> > function > >> >> >> >> > convertion. I realy apreciate any input with this issue. I'm wondering if there may be a problem with your regional settings. From the Start Menu ...Select Control_Panel ...Dbl-Click: Regional and Language Options See if anything looks irregular. If NO ...Click the [Customize] button and see if those settings make sense. Please let us know what you find. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) > Unfortunately, I had try this and it does not change it. You think is any > other setting? As part of the test, if I enter 11-29-2007 it does [quoted text clipped - 156 lines] >> >> >> >> >> > function >> >> >> >> >> > convertion. I realy apreciate any input with this issue. Ron, first I want to thank you very much for all your support. Your suggestion on going to regional settings was the problem. I compared the sttings with another computer to find the discrepancies on the setings. So on the Regional and Language Options on the Regional Options tab, the Short date displayed 2007/12/29 and Longdate displayed 2007, December29 Saturday. I Change it to be the Sort date 12/29/2007 and Long date to be Saturday, December 29 2007. Then I went to Excel and selected the column,with dates click on Data, Text to column, Finish and that change my dates. I hope other people benefit from this. Again thank you for all your great input, patience and great troubleshoot skills. Sincerely Lumi > I'm wondering if there may be a problem with your regional settings. > [quoted text clipped - 176 lines] > >> >> >> >> >> > function > >> >> >> >> >> > convertion. I realy apreciate any input with this issue. I'm glad you figured it out!....Thanks for the feedback *********** Regards, Ron XL2003, WinXP > Ron, first I want to thank you very much for all your support. Your > suggestion on going to regional settings was the problem. I compared the [quoted text clipped - 189 lines] > > >> >> >> >> >> > function > > >> >> >> >> >> > convertion. I realy apreciate any input with this issue. |
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.