 |
 | 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 / March 2008Converting "Date and Time" to just DATE?
I have a large number of cells with date and time like this, "Jan 12, 2001 5:55 pm" I want to truncate the time in the cell and only make it display the date. Can someone help me? You can apply a custom format to the cells so that only the date is displayed (but the time is still included within the data), eg mmm dd, yyyy. If you want to get rid of the time part completely, then use this formula in an adjacent column: =INT(A1) and format the cell as a date, and then copy down. Hope this helps. Pete > I have a large number of cells with date and time like this, "Jan 12, > 2001 5:55 pm" > I want to truncate the time in the cell and only make it display the > date. > > Can someone help me? No, that didn't work. Here is an example of the cells. 5/3/08 6:23 AM 3/3/08 7:06 PM 2/3/08 3:55 PM 2/3/08 10:07 AM 29-02-2008 07:55 AM 29-02-2008 07:51 AM 29-02-2008 07:37 AM 28-02-2008 07:57 AM 28-02-2008 07:56 AM 28-02-2008 07:54 AM 27-02-2008 08:39 PM 27-02-2008 02:58 PM 27-02-2008 02:56 PM 27-02-2008 11:32 AM 27-02-2008 11:31 AM 26-02-2008 10:19 AM 26-02-2008 07:23 AM 25-02-2008 05:28 PM 25-02-2008 05:26 PM Notice some are m/d/year and some are d/m/year thanks >You can apply a custom format to the cells so that only the date is >displayed (but the time is still included within the data), eg mmm dd, [quoted text clipped - 17 lines] >> >> Can someone help me? I suspect these are probably text values that just look like dates. What happens if you highlight the column, do Format | Cells | Number tab then click on Number with 2dp and OK? Do you get numbers, or do the displays stay the same? Pete > No, that didn't work. > [quoted text clipped - 47 lines] > > - Show quoted text - They're text it seems. Can I do anything with that? >I suspect these are probably text values that just look like dates. >What happens if you highlight the column, do Format | Cells | Number [quoted text clipped - 54 lines] >> >> - Show quoted text - Okay, try this: Ensure that you have 2 blank columns next to the column with dates, inserting new columns if necessary. Then highlight the column with your dates in, click on Data | Text-to-columns and in the Wizard click on Delimited in the first panel, then Next, then click on Space and Next, and in the final panel click Date with DMY, amend the Destination to $B$1 (if your dates are in column A), then click Finish. You should end up with just dates in column B and the times in column C. Hope this helps. Pete > They're text it seems. > [quoted text clipped - 60 lines] > > - Show quoted text - >Okay, try this: > [quoted text clipped - 12 lines] > >Pete Here is what that did Original new new 2/29/08 12:00 AM 1/0/00 7:55 AM AM It didn't work but maybe their is an extra step needed? In the examples you quoted earlier you had day-month-year format, but now you are showing month-day-year - you need to select the appropriate format in stage 3 of the Text-to-columns wizard. Pete > >Okay, try this: > [quoted text clipped - 20 lines] > > - Show quoted text - If the originating data is in the M/D/Y format what you said will work, but if it's in the D/M/Y format it displays "Jan 1, 00 1pm" etc >In the examples you quoted earlier you had day-month-year format, but >now you are showing month-day-year - you need to select the [quoted text clipped - 26 lines] >> >> - Show quoted text - So, is the data in a mixture of formats? Would you like to send the file to me so I can have a look at it? pashurst <at> auditel.net Change the obvious. I only need the date/time data. Pete > If the originating data is in the M/D/Y format what you said will > work, but if it's in the D/M/Y format it displays "Jan 1, 00 1pm" etc [quoted text clipped - 31 lines] > > - Show quoted text - I appreciate the help but in the time we've tried to fix it I simply re-typed all the collumns. Again, I do appreciate the help you gave. >So, is the data in a mixture of formats? > [quoted text clipped - 43 lines] >> >> - Show quoted text - Okay, then, Robert - better luck next time. Pete > I appreciate the help but in the time we've tried to fix it I simply > re-typed all the collumns. [quoted text clipped - 50 lines] > > - Show quoted text - |
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.