Hi

I run Excel 2K

I have a column of dates that are downloaded from a mainframe. The date
format of these are 18/08/2009 7:58:00 AM.

I am trying to contruct a pivot table that looks at these dates and allows
me to pick a date range for the pivot table. I have the formula to do this
which I have used many times. The problem I have is because there is a time
included in the download it does not allow my formula to work correctly.

How can I reformat the date to exclude the time component. Or, is there a
formula that I can use that strips the time component?

I have tried a custom format which forces it to be displayed as 18/08/2009
in the cell however it still shows 18/08/2009 7:58:00 AM in the edit bar.


Thanks

John

Formatting only changes the *display*. It doesn't change the true underlying
value of the cell.

You can strip off the time like this:

A1 = 18/08/2009 7:58:00 AM

=INT(A1)

Format as Date

--
Biff
Microsoft Excel MVP


"John Calder" wrote in message
...
Hi

I run Excel 2K

I have a column of dates that are downloaded from a mainframe. The date
format of these are 18/08/2009 7:58:00 AM.

I am trying to contruct a pivot table that looks at these dates and allows
me to pick a date range for the pivot table. I have the formula to do this
which I have used many times. The problem I have is because there is a
time
included in the download it does not allow my formula to work correctly.

How can I reformat the date to exclude the time component. Or, is there a
formula that I can use that strips the time component?

I have tried a custom format which forces it to be displayed as 18/08/2009
in the cell however it still shows 18/08/2009 7:58:00 AM in the edit bar.


Thanks

John

Excellent !

Once again....well done !

Thanks

John


"T. Valko" wrote:

Formatting only changes the *display*. It doesn't change the true underlying
value of the cell.

You can strip off the time like this:

A1 = 18/08/2009 7:58:00 AM

=INT(A1)

Format as Date

--
Biff
Microsoft Excel MVP


"John Calder" wrote in message
...
Hi

I run Excel 2K

I have a column of dates that are downloaded from a mainframe. The date
format of these are 18/08/2009 7:58:00 AM.

I am trying to contruct a pivot table that looks at these dates and allows
me to pick a date range for the pivot table. I have the formula to do this
which I have used many times. The problem I have is because there is a
time
included in the download it does not allow my formula to work correctly.

How can I reformat the date to exclude the time component. Or, is there a
formula that I can use that strips the time component?

I have tried a custom format which forces it to be displayed as 18/08/2009
in the cell however it still shows 18/08/2009 7:58:00 AM in the edit bar.


Thanks

John

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"John Calder" wrote in message
...
Excellent !

Once again....well done !

Thanks

John


"T. Valko" wrote:

Formatting only changes the *display*. It doesn't change the true
underlying
value of the cell.

You can strip off the time like this:

A1 = 18/08/2009 7:58:00 AM

=INT(A1)

Format as Date

--
Biff
Microsoft Excel MVP


"John Calder" wrote in message
...
Hi

I run Excel 2K

I have a column of dates that are downloaded from a mainframe. The date
format of these are 18/08/2009 7:58:00 AM.

I am trying to contruct a pivot table that looks at these dates and
allows
me to pick a date range for the pivot table. I have the formula to do
this
which I have used many times. The problem I have is because there is a
time
included in the download it does not allow my formula to work
correctly.

How can I reformat the date to exclude the time component. Or, is there
a
formula that I can use that strips the time component?

I have tried a custom format which forces it to be displayed as
18/08/2009
in the cell however it still shows 18/08/2009 7:58:00 AM in the edit
bar.


Thanks

John