format date time

[IT05]

5/12/2005 1:20:33 PM = text field
10/20/2004 10:02:40 AM = text field

I need to extract just the date and ignore the time.

Tried =MID(F2,1,9) and it worked for 5/12/2005 1:20:33 PM. Output showed
5/12/2005

Apparently this would not work for 10/20/2004 10:02:40 AM. Output would show
10/20/200.

Is there a work-around for this or a different formula that can be used?

[daddylonglegs]

Try

=DATEVALUE(F2)

format as date

[David McRitchie]

Hi IT05, (certainly hope that's not your real name),

Dates are stored as days past a base date, and time is fractional days both
can be in the same cell as a number.

If format is all you want then format as mm/dd/yyyy but if
you need the value then you can use =MOD(A1,1) which
will remove decimal places and format as mm/dd/yyyy
dates are numbers so you can change the format without reentering
because you are not switching between text and numbers (or numbers and text)..

More information on Date and Time
http://www.mvps.org/dmcritchie/excel/datetime.htm
http://www.cpearson.com/excel/datetime.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"IT05" <[email protected]> wrote in message news:[email protected]...
> 5/12/2005 1:20:33 PM = text field
> 10/20/2004 10:02:40 AM = text field
>
> I need to extract just the date and ignore the time.
>
> Tried =MID(F2,1,9) and it worked for 5/12/2005 1:20:33 PM. Output showed
> 5/12/2005
>
> Apparently this would not work for 10/20/2004 10:02:40 AM. Output would show
> 10/20/200.
>
> Is there a work-around for this or a different formula that can be used?
>
>

[daddylonglegs]

If you want to keep the result as text

=LEFT(A1,FIND(" ",A1)-1)

or

=TEXT(DATEVALUE(A1),"m/d/yyyy")

[Dave Peterson]

One more:

=INT(--A1)

And format as a date.

The -- converts the text to numbers and the int() drops the fractional portion
(the time).

IT05 wrote:
>
> 5/12/2005 1:20:33 PM = text field
> 10/20/2004 10:02:40 AM = text field
>
> I need to extract just the date and ignore the time.
>
> Tried =MID(F2,1,9) and it worked for 5/12/2005 1:20:33 PM. Output showed
> 5/12/2005
>
> Apparently this would not work for 10/20/2004 10:02:40 AM. Output would show
> 10/20/200.
>
> Is there a work-around for this or a different formula that can be used?

--

Dave Peterson

[David McRitchie]

Oops serious problem with my reply as MOD(A1,1) would retain only
the time not the date. Dave Peterson 's integer response would be