| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting "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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting "Date and Time" to just DATE?
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 On Mar 5, 11:34*am, Robert Blass wrote: 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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting "Date and Time" to just DATE?
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 On Wed, 5 Mar 2008 03:42:37 -0800 (PST), Pete_UK sayd the following: 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 On Mar 5, 11:34*am, Robert Blass wrote: 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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting "Date and Time" to just DATE?
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 On Mar 5, 11:45*am, Robert Blass wrote: 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 On Wed, 5 Mar 2008 03:42:37 -0800 (PST), Pete_UK sayd the following: 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 On Mar 5, 11:34*am, Robert Blass wrote: 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?- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting "Date and Time" to just DATE?
They're text it seems.
Can I do anything with that? On Wed, 5 Mar 2008 03:49:04 -0800 (PST), Pete_UK sayd the following: 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 On Mar 5, 11:45*am, Robert Blass wrote: 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 On Wed, 5 Mar 2008 03:42:37 -0800 (PST), Pete_UK sayd the following: 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 On Mar 5, 11:34*am, Robert Blass wrote: 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?- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Converting "Date and Time" to just DATE?
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 On Mar 5, 11:56*am, Robert Blass wrote: They're text it seems. Can I do anything with that? On Wed, 5 Mar 2008 03:49:04 -0800 (PST), Pete_UK sayd the following: 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 On Mar 5, 11:45*am, Robert Blass wrote: 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 On Wed, 5 Mar 2008 03:42:37 -0800 (PST), Pete_UK sayd the following: 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 On Mar 5, 11:34*am, Robert Blass wrote: 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?- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Converting Text to Date using "Text to Columns" | Excel Discussion (Misc queries) | |||
| If date is in the month of Sept, then "1", otherwise "2" | Excel Worksheet Functions | |||
| Excel 2003 - Charts - "Data Range" and Date/Time data on X-Axis | Charts and Charting in Excel | |||
| "NOW" or "TODAY" date function | Excel Worksheet Functions | |||
| Showing "Created by User, Date, Time" in Footer | Excel Discussion (Misc queries) | |||
Hybrid Mode
