Removing time element of Long Date Format

Question

I have a list of data within the last three years, but all the dates have different times on it. I am trying to write a macro that will delete all the times. I tried changing the format of the cell but it didn't work.

EX. the list looks like:

10/12/2011 08:41 PM Eastern Time
10/12/2011 08:41 PM Eastern Time
10/12/2011 08:41 PM Eastern Time
10/12/2011 08:41 PM Eastern Time
10/12/2011 08:41 PM Eastern Time
10/12/2011 08:41 PM Eastern Time
10/12/2011 08:41 PM Eastern Time
10/12/2011 08:41 PM Eastern Time

And I need to delete the 08:41 PM Eastern Time or equivalent from each. All the times are different though. Any thoughts?

Answer 1

If actually date format, try =INT(A1) then format to suit.

This should cover either text or time format:

=IF(CELL("type",A1)="l",LEFT(A1,FIND(" ",A1)),INT(A1))  

or even:

=TEXT(IF(CELL("type",A1)="l",LEFT(A1,FIND(" ",A1)),INT(A1)),"mm/dd/yyyy")  

if the output is to be formatted mm/dd/yyyy.

Answer 2

Why not simply:

=Left(A1,10)

Assuming all dates look the same (mm/dd/yyyy) format

Answer 3

Just in case it isn't always the first ten characters (examples: 1/1/2011 or 3/14/12), you can use this formula. Be sure to format the cell as date:

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

Answer 4

If the field is an excel date and not plain text, you can use the trunc function. It should trim the time portion off.

=TRUNC(A1)

Answer 5

Not sure why this is not mentioned in any of the answers as its by far the fastest and easiest way to do this. You can use the DATEVALUE to convert the dates from text to an excel date if they are not already in an excel date format.

Once the date is in a number format (eg 42753.597534 which excel recognizes as January 18, 2017 2:20:27 PM): Excel equates dates to numbers and 1 is equivalent to 24 hours(1 day), while anything less than 1 is used for time. You can use ROUNDDOWN(A1,0) which will strip any non-integer part of the date which implicitly gets rid of the time (turning the above number into 42753 or January 18 , 2017.