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

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?

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?

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 -

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 -

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 -

On Wed, 5 Mar 2008 04:57:46 -0800 (PST), Pete_UK
sayd the following:

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

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

On Mar 5, 3:08*pm, Robert Blass wrote:
On Wed, 5 Mar 2008 04:57:46 -0800 (PST), Pete_UK
sayd the following:





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

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?- Hide quoted text -

- 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


On Wed, 5 Mar 2008 07:53:58 -0800 (PST), Pete_UK
sayd the following:

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

On Mar 5, 3:08*pm, Robert Blass wrote:
On Wed, 5 Mar 2008 04:57:46 -0800 (PST), Pete_UK
sayd the following:





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

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?- Hide quoted text -

- 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

On Mar 5, 4:53*pm, Robert Blass wrote:
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

On Wed, 5 Mar 2008 07:53:58 -0800 (PST), Pete_UK
sayd the following:



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

On Mar 5, 3:08*pm, Robert Blass wrote:
On Wed, 5 Mar 2008 04:57:46 -0800 (PST), Pete_UK
sayd the following:

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

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?- Hide quoted text -

- Show quoted text -- Hide quoted text -

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

On Wed, 5 Mar 2008 09:10:39 -0800 (PST), Pete_UK
sayd the following:

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

On Mar 5, 4:53*pm, Robert Blass wrote:
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

On Wed, 5 Mar 2008 07:53:58 -0800 (PST), Pete_UK
sayd the following:



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

On Mar 5, 3:08*pm, Robert Blass wrote:
On Wed, 5 Mar 2008 04:57:46 -0800 (PST), Pete_UK
sayd the following:

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

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?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Okay, then, Robert - better luck next time.

Pete

On Mar 5, 6:58*pm, Robert Blass wrote:
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.

On Wed, 5 Mar 2008 09:10:39 -0800 (PST), Pete_UK
sayd the following:



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

On Mar 5, 4:53*pm, Robert Blass wrote:
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

On Wed, 5 Mar 2008 07:53:58 -0800 (PST), Pete_UK
sayd the following:

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

On Mar 5, 3:08*pm, Robert Blass wrote:
On Wed, 5 Mar 2008 04:57:46 -0800 (PST), Pete_UK
sayd the following:

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

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?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -