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....--
Craig Lloyd
------------------------------------------------------------------------
Craig Lloyd's Profile: www.excelforum.com/member.php...oamp;userid=29769
View this thread: www.excelforum.com/showthread...hreadid=494842Remember that date/time is just a number.

When you see: 12/20/05 8:05 AM
the underlying number is 38706.3372719907

If you want to discard the time, use the =INT() function

--
Gary''s Studentquot;Craig Lloydquot; wrote:

gt;
gt; Hi all,
gt;
gt; I have set up an Excel Spreadsheet that imports data from a sql
gt; database using the 'external data' function.
gt;
gt; The problem is that the dates being retrieved from the db are in the
gt; format 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 the time but whilst the date displays correctly if the cell is
gt; selected the original date format is still displayed.
gt;
gt; This is preventing me from creating rules and using counting formulas
gt; etc
gt;
gt; Can anyone help???? Please....
gt;
gt;
gt; --
gt; Craig Lloyd
gt; ------------------------------------------------------------------------
gt; Craig Lloyd's Profile: www.excelforum.com/member.php...oamp;userid=29769
gt; View this thread: www.excelforum.com/showthread...hreadid=494842
gt;
gt;


Select the date range and go to Data|Text to Columns. Skip to the 3rd
dialogue box and select the Date radio button and make sure the
selected format is MDY. Click Finish.--
Vito
------------------------------------------------------------------------
Vito's Profile: www.excelforum.com/member.php...oamp;userid=29182
View this thread: www.excelforum.com/showthread...hreadid=494842
Gary - How do i use he INT function?--
Craig Lloyd
------------------------------------------------------------------------
Craig Lloyd's Profile: www.excelforum.com/member.php...oamp;userid=29769
View this thread: www.excelforum.com/showthread...hreadid=494842
Craig Lloyd Wrote:
gt; Gary - How do i use he INT function?For that function, you will need to insert a helper column.

If you use the Date|Text to Columns method I suggested, you will not
need to add a column. The changes are in place.--
Vito
------------------------------------------------------------------------
Vito's Profile: www.excelforum.com/member.php...oamp;userid=29182
View this thread: www.excelforum.com/showthread...hreadid=494842If A1 contains the date/time, then in another cell enter =INT(A1).
--
Gary''s Studentquot;Craig Lloydquot; wrote:

gt;
gt; Gary - How do i use he INT function?
gt;
gt;
gt; --
gt; Craig Lloyd
gt; ------------------------------------------------------------------------
gt; Craig Lloyd's Profile: www.excelforum.com/member.php...oamp;userid=29769
gt; View this thread: www.excelforum.com/showthread...hreadid=494842
gt;
gt;