How to Easily Remove Time from Dates in Excel

Understanding Excel’s Date and Time Serial System

To effectively manipulate date and time data, it is essential to grasp how Excel stores these values internally. Unlike plain text, dates and times are stored as numerical Serial Values. The integer part of this number represents the date, counting the number of days since January 1, 1900. For instance, the number 44927 represents January 1, 2023. This integer portion is the part we wish to retain when removing the time stamp.

The time component is stored as the fractional part (the decimal) of the same serial number. This decimal fraction represents the proportion of a 24-hour day that has elapsed. For example, 0.5 represents noon (12:00 PM), and 0.25 represents 6:00 AM. When a cell displays both a date and a time, say 1/5/2023 6:12 AM, the underlying serial value might look like 44931.25833. Our goal is therefore to mathematically strip away the decimal part (.25833) while preserving the integer (44931).

This understanding immediately suggests several mathematical approaches. The simplest method involves using the INT function, which truncates any decimal value and returns only the integer portion of the number. If cell A2 contains the date-time serial value, the formula =INT(A2) will return the date serial value without the time. Although this is the mathematically cleanest way to remove the time, applying specific formatting (like “mm/dd/yyyy”) is still necessary to display the result as a recognizable date rather than a simple number.

Method 1: Utilizing the INT Function for Quick Truncation

While we will shortly introduce a more complex, text-based solution that ensures specific formatting is applied simultaneously, the INT function is arguably the fastest and most efficient way to achieve date truncation. Since the date is the integer and the time is the fraction, the INT function isolates the date serial number instantly. This method is preferred when data efficiency and computational speed are paramount, especially when working with millions of rows.

The process is straightforward: insert a new column (say, Column B) next to your original data (Column A). In the first cell of Column B, enter =INT(A2). Once the formula is applied, you will notice that the result in B2 is likely a large five-digit number—the raw serial value. This is expected. The final step is to apply a standard Date format (e.g., Short Date or Custom Format “mm/dd/yyyy”) to all cells in Column B to make the serial number readable as a calendar date.

Alternatively, the TRUNC function operates identically to INT function for positive numbers, yielding the same result: removal of the fractional time component. While simple subtraction using the TIMEVALUE function (i.e., =A2 - TIMEVALUE(A2)) also works, the INT function provides the most direct route by leveraging the foundational structure of the Excel date system, requiring minimal computational overhead.

Method 2: Applying the Advanced TEXT and DATEVALUE Formula Combination

While the INT method is effective, it requires two steps: applying the formula and then applying number formatting. The following formula combination, though appearing more complex, is superior for generating a final, formatted date output in a single step. This approach is highly reliable because it treats the date-time entry initially as a string of text, isolates the date portion, and then converts it back into a properly recognized date serial number.

The following formula can be used to remove the time from a date with a time in Excel:

=TEXT(DATEVALUE(TEXT(A2,"mm/dd/yyyy")),"mm/dd/yyyy")

This particular formula chain will effectively remove the time from the date and time located in cell A2. It works through three distinct layers. First, the inner TEXT function converts the date-time serial number into a text string using a specified format (“mm/dd/yyyy”). Crucially, converting it to text eliminates the fractional time portion immediately. Next, the DATEVALUE function takes this clean date text string and converts it back into a valid date serial number. Finally, the outer TEXT function takes that serial number and formats it back into the desired text output, ensuring consistent display.

For example, if cell A2 contains the entry 1/5/2023 6:12 AM, which has an underlying serial value including a time fraction, this formula will first strip the time upon conversion to text, and subsequently convert the resulting clean date serial value (44931) back into the formatted output 1/5/2023. Although this method returns a text value (not a numerical date serial value), which can sometimes complicate arithmetic calculations, its primary benefit is generating a perfectly formatted, clean date string ready for display or concatenation.

Step-by-Step Example: Implementing the TEXT and DATEVALUE Formula

To illustrate the practical application of this combined function approach, consider a common scenario where you have imported data logs or transactional records containing date and time stamps in a single column. Suppose we have the following list of date and times in Excel, presented in column A:

To remove the time from the first cell in A2, we can type the following formula into cell B2, assuming column B is our designated output column:

=TEXT(DATEVALUE(TEXT(A2,"mm/dd/yyyy")),"mm/dd/yyyy")

Once entered, this formula immediately cleans the data entry in A2. The powerful advantage of spreadsheet software is the ability to handle data sets quickly. We can then drag and fill this formula down to each remaining cell in column B to process the entire dataset in seconds:

Notice that column B now uniformly displays each date entry originally found in column A without the accompanying time stamp. This outcome is achieved without requiring any manual formatting steps in the ribbon, making it an excellent solution for repeatable tasks and standard reporting templates where consistent date format presentation is mandatory.

Customizing Date Display Formats (mm vs m)

The structure of the TEXT function allows for precise control over how the resulting date string is presented. In the previous example, we used the format mask “mm/dd/yyyy”. The crucial element here is the use of ‘mm’, which explicitly tells Excel to display each month with two digits, ensuring leading zeros are included for single-digit months, e.g., displaying 01 instead of 1 for January. This convention is vital for data standardization, especially when exporting data to systems that require fixed-length fields.

However, if your specific reporting requirement dictates that you should only display one digit for the month (removing the leading zero for months 1 through 9), you can easily modify the format string. You should instead use ‘m/dd/yyyy’ as the format mask in both the inner and outer TEXT functions:

=TEXT(DATEVALUE(TEXT(A2,"m/dd/yyyy")),"m/dd/yyyy")

The following screenshot illustrates how using this modified formula affects the output presentation:

Notice clearly that column B now only displays each month with one digit when applicable (i.e., January is now 1, not 01). Months that inherently have two digits (like October, November, and December) will naturally still be displayed with two digits regardless of whether ‘m’ or ‘mm’ is used, as the latter controls the presence of the leading zero, not the overall length. This flexibility ensures the output meets any specific display requirements without altering the underlying accuracy of the date.

Method 3: Using Text to Columns for Permanent Data Modification

If your objective is to permanently modify the data set by replacing the original date-time values with clean date values, rather than using formulas that rely on the original data, the Text to Columns feature is the appropriate tool. This utility is excellent for one-time data cleaning tasks where the time component must be discarded entirely from the original column.

To execute this method, select the column containing your date-time data. Navigate to the Data Tab and choose the Text to Columns option. Within the wizard, select Delimited, then choose Space as your delimiter, since dates and times are usually separated by a space. The wizard will then show two resultant columns: one containing the dates and one containing the times. In the final step of the wizard, you must select the column containing the time data and specify that this column should Do Not Import (Skip). This action prevents the time data from being loaded back into the spreadsheet, effectively deleting it while leaving the dates intact in the original column location.

It is important to understand that this method is destructive; it overwrites the original data in place. Therefore, it is strongly recommended to copy the original date-time column before running Text to Columns, serving as a backup in case the separation process needs to be reversed or reviewed. After running the process, you may still need to apply a standard Date number format to the resulting column, as the process sometimes defaults to general number formatting.

Method 4: Advanced Alternatives Using Power Query or VBA

For users dealing with extremely large data sets or those requiring automation of the data cleaning process, more advanced tools within Excel offer sophisticated solutions. Power Query (also known as Get & Transform Data) is an outstanding tool for data manipulation. Within the Power Query Editor, you can easily select a date-time column, right-click, and choose Transform -> Date -> Date Only. This action automatically truncates the time component based on M language functions (specifically Date.From), maintaining the date integrity and allowing for refreshable reports.

Alternatively, for those who require custom scripting or integration into larger macros, Visual Basic for Applications (VBA) provides programmable control. A simple VBA loop can iterate through all selected cells and use the built-in Int function or the DateValue function to strip the time. For instance, the line of code ActiveCell.Value = Int(ActiveCell.Value) executed across a range will instantly and permanently remove the time component from every cell, making it an efficient solution for large-scale, automated cleaning tasks.

Conclusion: Choosing the Right Technique for Your Data Needs

The decision of which method to employ—whether the INT function, the combined TEXT/DATEVALUE formula, or the Text to Columns utility—should be guided by your specific requirements for data presentation and calculation.

If you need the result to be a numerical date serial value (allowing for future arithmetic calculations like date differences), the =INT(A2) formula combined with explicit date formatting is the ideal, non-destructive choice. If, however, you require a formatted text string output that displays the date perfectly according to strict specifications (like “01/05/2023”), the longer, chained formula =TEXT(DATEVALUE(TEXT(A2,"format")),"format") provides a superior single-step solution. Lastly, if the objective is permanent data reduction and removal of the time stamp from the original source column, Text to Columns is the fastest way to perform the irreversible modification.

Mastering these various techniques ensures that you can handle date and time data in Excel with confidence, generating clean, consistent, and analysis-ready datasets tailored precisely to your analytical goals.