software

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

quot;Craigquot; gt; wrote in message
...
gt; Hi all,
gt;
gt; I have set up an Excel Spreadsheet that imports data from a sql database
gt; using the 'external data' function.
gt;
gt; The problem is that the dates being retrieved from the db are in the
gt; format
gt; 5/12/2005 12:29:59 AM
gt;
gt; I have set excel to display the dates as short dates, ie 05/12/2005
gt; without
gt; the time but whilst the date displays correctly if the cell is selected
gt; the
gt; original date format is still displayed.
gt;
gt; This is preventing me from creating rules and using counting formulas etc
gt;
gt; 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)