Your post raises several questions. First, why does the date format
still display the time? No idea, except that perhaps you don't have
the cell formatted the way you think you do. I've never seen such
behavior before.

Next, by "pull out the time", do you mean extract the time to another
cell? In this case, with the original data in A1, use =MOD(A1,1) and
format this cell for time. It will return the time in A1 without any
date value. (Actually, the date value will be 0, which is interpreted
as 0-Jan-1900). If by "pull out the time", you mean to remove the time
component and end up with just a date, use =INT(A1) and format for
Date. This will return midnight of the date in A1.

Both of these formulas change the actual value from A1. Regardless of
any formatting that is applied, formatting alone does not change the
value of a cell. If A1 has 12-May-2010 12:34 PM, changing the format
to a simple date cause the DISPLAY to be just a date, but the VALUE of
the cell still has the time component. This may be important if you
have calculations based on that cell.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Fri, 14 May 2010 14:30:01 -0700, Jen_T
wrote:

I have cells in a worksheet that are formatted as date but still show as date
and time. How do I pull out the time.

E.g.
04/07/2009 1:00:00 AM
I have tried the =left() and also =right () formulas with no luck.