Crop Time from Date and Time Cell

[Scottmk]

In a cell I have this info: "8/29/2008 5:40:40 PM" pasted in from a website. It has the custom format "m/d/yyyy h:mm" I would like to eliminate the time from the cell, but when I try to use "=LEFT()," the format goes to a weird number. I have tried changing the format of that cell to "m/d/yyyy" but no luck. Any ideas on how to remove the time while preserving the date would be appreciated. Thanks.

[Palmetto]

There are several options: here are two

1. LEFT(A1,5) - format the cell to the desired date

2. =DATE(YEAR(A1),MONTH(A1),DAY(A1))

[DonkeyOte]

It's not clear if the values imported are true date time values (ie ISNUMBER(cell) returns TRUE) or not.

If they are true datetime values (ie ISNUMBER returns True) then to use LEFT you would need to coerce the output before you could format result as date, eg:

=0+LEFT(A1,5)

Another alternative would be:

=INT(A1)

Given Dates are Integers & Time is Decimal

ie you can just as easily retrieve only the time by pulling the decimal remainder

=MOD(A1,1)
format accordingly

If ISNUMBER returns FALSE then you will need to use an alternative approach, which will in part depend on your locale.