5 Ways to Strip Time from Dates in Excel

Time management is a common necessity, particularly when dealing with extensive datasets in Excel. There are often scenarios where you may need to remove the time component from date-time values to streamline your data analysis. Here are five methods to effectively strip the time from dates in Excel, each suited to different needs and proficiency levels in Excel.

Method 1: Using the INT Function

The INT function is the simplest way to round down a number to the nearest integer. In Excel, dates are stored as numbers where the integer part represents the date and the decimal part represents the time:

• INT Function Syntax: =INT(serial_number)
• Example: Assume cell A1 contains the date-time value 12/25/2023 11:22:33 AM, using the formula =INT(A1) in another cell will give you 12/25/2023.

Method 2: Using the ROUND Function

The ROUND function rounds a number to a specified number of digits. Using ROUND with zero as the second argument achieves the same effect as INT for removing time:

• ROUND Function Syntax: =ROUND(number, num_digits)
• Example: For the date-time value in A1, the formula =ROUND(A1,0) returns the date without time.

Method 3: Creating a Custom Excel Function

For power users or analysts dealing with repetitive tasks, crafting a custom function (User Defined Function or UDF) can automate the process of stripping time:

• Steps:
  1. Open the Visual Basic Editor by pressing Alt + F11.
  2. Insert a new module (Insert > Module).
  3. Type the following VBA code:

  
  Public Function StripTime(myCell As Range) As Date
      StripTime = Int(myCell.Value)
  End Function
  
  

• Save the module.
• Now you can use =StripTime(A1) in your Excel sheet to remove time from the date.

⚠️ Note: Custom functions require the use of VBA, which might not be enabled in your Excel settings. Remember to enable macros if they're disabled.

Method 4: Text to Columns

If you're dealing with data that has the date and time in a single cell, Text to Columns can be a handy method:

• Steps:
  1. Select the column with date-time values.
  2. Go to Data > Text to Columns.
  3. Choose 'Delimited', click 'Next'.
  4. Select 'Space' as the delimiter.
  5. Click 'Finish'.
  6. The date and time will now be in separate columns.
• Pros: No formula needed, but this method splits data into different columns.
• Cons: Altering the original data, which might not be desirable in some data integrity scenarios.

Method 5: Power Query

Power Query offers a robust way to clean and transform data in Excel, including stripping the time from dates:

• Steps:
  1. Select your data range.
  2. Go to Data > From Table/Range to load into Power Query.
  3. In Power Query Editor, select the column with the date-time value.
  4. Go to Transform > Date > Date Only.
  5. Load the transformed data back to Excel.
• Pros: Automates repetitive tasks, maintains data integrity, and allows for more complex transformations.

As we’ve explored, Excel provides various tools to strip time from dates, each with its own use-case scenario. INT and ROUND functions are simple and direct, suitable for quick edits. Custom VBA functions offer customization and automation for repetitive tasks, while Text to Columns is efficient for visually separating date and time. Power Query stands out for those looking to automate larger scale data manipulation with better record keeping.

In conclusion, choose your method based on the task at hand, your familiarity with Excel, and how much control you need over the data transformation process. Understanding these methods can greatly enhance your productivity when working with Excel date-time data.

Related Terms:

• strip date from datetime excel
• excel formula to extract time
• excel timestamp to date
• excel time format without date
• excel formula to shorten date
• excel trim date time to