Removing Time from Dates in Excel: Quick Guide

When dealing with dates in Microsoft Excel, often the need arises to separate the date from the time component, whether for reporting purposes, analysis, or to simplify data presentation. Excel offers several ways to achieve this, making it straightforward once you understand the formulas and tools available. Let's explore how you can remove time from dates in Excel through different methods.

Using the INT Function

The INT function in Excel is useful for stripping off the decimal portion of a number, which in the case of date and time data, represents the time:

• Select the cell where you want the result to appear.
• Enter the formula: =INT(A1) where A1 is the cell containing your date-time value.

This will return only the date without the time part. Here’s what happens:

📘 Note: Ensure the date format in Excel reflects your intention, otherwise, you might see a number instead of the date.

Formatting Cells to Show Only Date

Another simpler method is to change how Excel displays the cell:

• Select the cells or range of cells with the date-time entries.
• Right-click and choose 'Format Cells' or go to the 'Home' tab and click on 'Number Format'.
• Under the 'Number' tab, select 'Date' from the Category list. Choose the date format that suits your needs.

This method will not alter the underlying value but will change how it appears on the sheet.

Using Formulas to Extract Only Date

If you want to keep your original date-time data intact while creating a new column with only the date, here are some useful formulas:

Using TEXT Function

=TEXT(A1,"YYYY-MM-DD")

The TEXT function allows you to convert numbers into text with specific formatting:

• The first argument is the cell reference.
• The second argument is the format code.

This will return a string with only the date part formatted as you specify.

Using FLOOR Function

=FLOOR(A1,1)

The FLOOR function rounds down to the nearest multiple of significance, here set to 1:

• This will effectively remove the time part.

⚠️ Note: Be aware that this formula might require additional formatting to display the date as expected.

Pasting Dates as Values

If you've used any of the above formulas to remove time and want to keep only the date:

• Copy the cells containing the formulas.
• Right-click where you want to paste, choose 'Paste Special' > 'Values'.

This action will paste only the date value, without any formula.

Conclusion

Removing time from dates in Excel can be done in various ways, each with its own merits. Whether you're looking to format existing dates, perform calculations, or extract specific components of date-time values, Excel provides tools like the INT, TEXT, and FLOOR functions, along with straightforward formatting options, to help you achieve your data management goals efficiently. Understanding these methods ensures you have the flexibility to display data exactly as you need it, enhancing both the functionality and presentation of your spreadsheets.

Related Terms:

• remove date format in excel
• strip date from datetime excel
• excel truncate date
• excel formula to shorten date
• vba remove time from date
• excel timestamp to date