Imported Data Date Format Problem

Hi all,

I have set up an Excel Spreadsheet that imports data from a sql database
using the 'external data' function.

The problem is that the dates being retrieved from the db are in the format
5/12/2005 12:29:59 AM

I have set excel to display the dates as short dates, ie 05/12/2005 without
the time but whilst the date displays correctly if the cell is selected the
original date format is still displayed.

This is preventing me from creating rules and using counting formulas etc

Can anyone help???? Please....

"Craig" <Craig@discussions.microsoft.com> wrote in message
news:C15FE5A2-3CED-46C3-8441-98752B25B957@microsoft.com...
> Hi all,
>
> I have set up an Excel Spreadsheet that imports data from a sql database
> using the 'external data' function.
>
> The problem is that the dates being retrieved from the db are in the
> format
> 5/12/2005 12:29:59 AM
>
> I have set excel to display the dates as short dates, ie 05/12/2005
> without
> the time but whilst the date displays correctly if the cell is selected
> the
> original date format is still displayed.
>
> This is preventing me from creating rules and using counting formulas etc
>
> Can anyone help???? Please....

Dates are stored as integers and time as a fraction of a day. So, Excel
stores the date/time you quote as 38691.0208217593 where 38691 represents
the date and 0.0208217593 represents the time. To get just the date, if the
time/date is in A1 you can use the formula
=INT(A1)