Removing time element of Long Date Format

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</pre>

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


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.


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



Why not simply:


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


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)


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.


  • How do I run a function on a specific key press in JavaFX?
  • Getting org.testng.TestNGException: No free nodes found in:[DynamicGraph Exception
  • Stack Cards horizontally with some offset
  • How to calculate total Fridays between two dates in excel on weekly, biweekly and monthly basis?
  • LibGdx GLES2.0 cube texel stretching
  • How to add the custom button on google's(device) native application in android?
  • Excel distinct count with conditions
  • Migration to HRD - How to convert string-encoded keys to new application
  • several dataProvider per one Test in TestNG
  • Center align outputs in ipython notebook
  • custom UITableViewCell with image for highlighting
  • Parsing a CSV string while ignoring commas inside the individual columns
  • Illegal mix of collations for operation for date/time comparison
  • Incrementing object id automatically JS constructor (static method and variable)
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • Deserializing XML into class C#
  • vba code to select only visible cells in specific column except heading
  • Release, debug version and Authorization Google?
  • VBA Convert delimiter text file to Excel
  • Web-crawler for facebook in python
  • Function pointer “assignment from incompatible pointer type” only when using vararg ellipsis
  • Rearranging Cells in UITableView Bug & Saving Changes
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • trying to dynamically update Highchart column chart but series undefined
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • Hits per day in Google Big Query
  • How get height of the a view with gone visibility and height defined as wrap_content in xml?
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • How do I configure my settings file to work with unit tests?
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • How can I remove ASP.NET Designer.cs files?
  • python draw pie shapes with colour filled
  • Are Kotlin's Float, Int etc optimised to built-in types in the JVM? [duplicate]
  • Sorting a 2D array using the second column C++
  • Binding checkboxes to object values in AngularJs
  • How to Embed XSL into XML
  • java string with new operator and a literal