Removing time element of Long Date Format

datetime, excel, excel-formula

Solution

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.

Problem

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?

Original source