Don’t know how to remove time from date in Excel? Read this blog till the end to learn the best methods.

When it comes to storing data in an Excel spreadsheet, both time and date data can be included in the database. At times, both types of data are included in the same cell. However, you may not need the time data all the time. In fact, time data could cause you inconvenience during calculation, aggregation, and organization.

You can avoid all these problems just by removing time components from the date cell. Thus, you’ll have only the date data that can be used according to your needs to sort the data entries. Moreover, while you’re going to use the Excel data in an application that doesn’t need time data, removing the unnecessary time components will make the process convenient.

So, let’s not waste any more time and check out the major reasons behind removing time data from the date cell. After that, find out the easiest solutions to this problem.

Reasons to Remove Time from Date in Excel

• When Excel only needs to show dates without the time data, you need to remove the time component to simplify the presentation.
• In some cases, a dataset contains inconsistently stored dates, with time information in some entries. Without the time component, you can ensure consistency in formatting across the dataset.
• Sometimes, you may need to use Excel data in software that doesn’t support time data. In that case, removing the time component helps you avoid potential data integration issues.
• When you need to sort or compare dates, differences in the time portions of the dates can cause discrepancies. Removing time from the date lets you prevent that.
• While presenting the Excel data to a non-technical audience, removing the time data component will improve the readability of the Excel data.
• In certain cases, time information isn’t relevant to the data analysis. Moreover, it causes problems as the analysis only requires the date data. Hence, you need to remove the time from the date in Excel.
• To calculate the sum or average of days, consistent date data is what you need. Removing time from the date data will facilitate straightforward calculation and analysis.

Remove Time From Date in Excel Using Short Date Format

The Short Date is a number formatting option in the General drop-down menu of the Number commands block in the Home tab of the Excel desktop or web app.

You can use it to re-format any date entries to the MM/DD/YYY date convention. It follows the system date formatting so might change if you’re from a non-US region.

To use this formatting rule in Excel, highlight one or more columns where you’ve got the timestamps data.

To highlight more than one column, select the first column, press the Ctrl key on the keyboard, and select the next column.

You should already be on the Home tab of the Excel app. So, click on the General drop-down menu of the Number commands block.

On the context menu that shows up, choose the Short Date option. You’re all set!

The next time you import timestamp data from a third-party source into the same worksheet, the above columns will automatically hide time data from date values.

Simply, copy data from the source, and highlight the columns except for the headers. Now, right-click and choose Values (V) or Formulas (F) options under the Paste Options menu on the context menu.

Remove Time From Date in Excel Using Number Formatting

The Format Cells dialog comes with the Number formatting feature where you can modify how Excel should display a number in its cells. Here, you’ll find dedicated number formatting rules for Date entries as well.

Suppose you’ve got the date and time entries in columns C and D. Select these columns and press Ctrl + 1 to bring up the Format Cells dialog.

On the Format Cells dialog, select the Date entry under the Category column on the left side navigation panel.

On the right side menu, below the Type: field, you should see various Date formats. Choose the one you need and click OK to apply the changes.

The next time you import date and time data to these two columns, just paste the data using Ctrl + Alt + V keys and choose Values on the dialog box that pops up. Click OK and Excel will paste the date data only trimming the time part for each row of data.

Remove Time From Date in Excel Using Text to Columns

In Excel, the Text to Columns feature is a powerful tool that allows you to split the contents of a cell or a range of cells into multiple columns. The separation of content takes place based on a specified delimiter, like space, comma, etc. This feature is particularly useful when you have data in a single column that you want to separate into distinct columns.

To use Text to Columns to remove time entries from dates in Excel, highlight the target cell range. Then, click the Data tab and hit the Text to Columns button inside the Data Tools commands block.

You should see the Convert Text to Column Wizard dialog. Here, select the Delimited option and click Next.

On the next screen of the wizard, checkmark the Space checkbox and click Next.

Now, select Text under the Column data format menu. Click Finish.

Don’t forget to delete the split columns to the right of the date data range.

Remove Time From Date in Excel Using the TEXT Function

In Excel, the TEXT function is a tool used for converting a numeric value into a specific text format. It allows you to customize the display of dates, numbers, and other data in a cell by applying a specified format code.

The syntax of the TEXT function is straightforward: TEXT(value, format_text). The value parameter represents the numeric value you want to format, and the format_text is a code that defines how the value should be displayed on Excel.

Go to the column where you need only dates from timestamps. Give it a column header, like Date.

In the first row, just below the Date column header, enter the following TEXT formula:

=TEXT(C2,"dd/mm/yyyy")

The above formula has the input data reference of C2. If yours is a different one, change that accordingly. Upon hitting Enter, you get only the date from the date and time combination.

Now, use the fill handle to apply the formula to other timestamp entries in the corresponding rows and populate date-only data in the adjacent column.

Remove Time From Date in Excel Using Power Query

When importing a large timestamp dataset from an external SQL server or analyzing from the local worksheet, the best way to remove unnecessary time entries from dates is the Power Query tool.

It’s an inexpensive and easy-to-use data connectivity and transformation tool that enables you to import, transform, and combine data from various sources seamlessly. In Power Query, you can use the Transform tool to find and apply various number formatting to imported data that contains date, time, day, currency, duration, and many more.

In this tutorial, I’m going to transform the timestamp dataset to a date-only dataset from a local Excel worksheet. However, if you need to import a dataset from an external source, click the Data tab on the ribbon and click Get Data.

Hover your cursor over the appropriate source to expand the overflow menu. Now, choose the particular dataset server or source from the overflow menu.

When you’ve imported your dataset in Power Query, select one or multiple columns on which you must use the Transform tool.

Now, click the Transform tab on the Power Query ribbon and click the Data Type drop-down list. Click on the Date option on the context menu that shows up.

On the Change Column Type message box, click on the Replace current button. Excel will instantly transform the selected columns to date-only datasets from the timestamp dataset.

To export the transformed dataset from Power Query to the Excel worksheet, click the File tab and choose the Close & Load To option.

You should now see the Import Data dialog. There, select the Existing worksheet option and highlight a destination cell range for the data being imported.

Click OK on the Import Data dialog to get the transformed datasets. Right-click on the old columns and click Delete on the context menu to only keep the new dataset.

Remove Time From Date in Excel Using VBA

Do you want to use an Excel VBA script to automate the whole transformation process of timestamps to only date entries in Excel? Then, this method is just for you.

Here, you’ll find a script that pulls input data from columns C and D and generates output date data in MM/DD/YYYY format in columns E and F.

Here’s the script you can use freely without crediting me. However, a credit or hyperlink to this article on your content is always welcome.

Sub RemoveTime()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet6") ' Replace "Sheet6" with the actual sheet name
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row ' Assuming your data is in column C
    
    Dim i As Long
    For i = 2 To lastRow ' Assuming data starts from row 2
        ' Remove time and populate in columns E and F
        ws.Cells(i, 5).Value = Int(ws.Cells(i, 3).Value)
        ws.Cells(i, 6).Value = Int(ws.Cells(i, 4).Value)
    Next i
End Sub

Here’s how you can create a VBA macro using the above script:

1. Press Alt + F11 to get to the Excel VBA Editor.
2. Click the Insert button on the toolbar.
3. Choose Module in the Insert context menu.
4. Copy and paste the above script inside the blank module.
5. Click the Save button.
6. On the Microsoft Excel pop-up, click the Go back button.

7. On the Save As dialog, click the Save as type drop-down and choose XLSM file type.
8. Click the Save button.
9. Close the Excel VBA Editor.

You’ve successfully created a VBA macro. Now, follow these steps to execute it:

1. Press the Alt + F8 keys to launch the Macro dialog box.
2. Select the RemoveTime macro.
3. Hit the Run button to execute the macro.

Here’s how to modify this Excel VBA script:

• "Sheet6" should replaced with the worksheet name on which you’re working.
• If your dataset is in any other columns than C and D, change the column name in (ws.Rows.Count, "C") and Excel will automatically consider the adjacent column on the right.
• Similarly, to change the destination columns, change ws.Cells(i, 5) and ws.Cells(i, 6). For example, if the destinations you want are G and H, use ws.Cells(i, 7) and and ws.Cells(i, 8).

If your timestamps are in a single column in the worksheet, use the following Excel VBA script instead:

Sub RemoveTimeAndPopulateSingleColumn()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet6") ' Replace "Sheet6" with the actual sheet name
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row ' Assuming your data is in column C
    
    Dim i As Long
    For i = 2 To lastRow ' Assuming data starts from row 2
        ' Remove time and populate in the same column
        ws.Cells(i, 4).Value = Int(ws.Cells(i, 3).Value)
    Next i
End Sub

Conclusions

Excel file often contains date and time data. When both data are included together unnecessarily, it can annoy the users. Also, when spreadsheet files are shared with other people, the recipients may not need time data as only the date is sufficient for their work.

In such cases, you must remove the time and keep the date data. Unless you know how to remove time from data in Excel, it’s not possible to segregate these two data and remove the unwanted one.

As understandable from the above discussion, you can remove time from date in an Excel file using different methods. All you need to do is to follow the steps mentioned without a miss.

Which of these methods do you find to be the easiest? Tell us in the comment section. Also, don’t forget to share your experience of implementing the solutions.