Time Format Problem

[John Calder]

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

 

[T. Valko]

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

 

[John Calder]

Excellent !

Once again....well done !

Thanks

John

 

[T. Valko]

You're welcome. Thanks for the feedback!