5 Simple Ways to Remove the Time from a Date in Excel

Excel dates are usually polite little things until they arrive from a report wearing a long timestamp like a tuxedo to a backyard barbecue. You expected 6/3/2026, but Excel gives you 6/3/2026 2:47:18 PM. Technically, it is not wrong. Emotionally, however, it is doing too much.

Whether you are cleaning exported sales data, preparing a project tracker, building a pivot table, or trying to make a report look less like it escaped from a database basement, knowing how to remove the time from a date in Excel is a small skill that saves a surprising amount of frustration. The trick is understanding what you want: do you want to merely hide the time, or do you want to permanently remove it from the value?

This guide walks through five simple, reliable methods. You will learn when to use cell formatting, formulas like INT and TRUNC, Text to Columns, Find and Replace, and Power Query. We will also cover common mistakes, practical examples, and real-world cleanup tips so your dates behave like dates instead of dramatic diary entries.

Why Excel Shows Time with Dates

Excel stores dates and times as numbers. The date is the whole-number portion, and the time is the decimal portion. For example, a date-time value such as 6/3/2026 12:00 PM may be stored as a serial number where the date is the integer and noon is represented by .5, because noon is halfway through the day.

That means removing time is usually about removing the decimal portion, changing how the value is displayed, or separating the date from the timestamp. Once you understand that, Excel stops feeling mysterious and starts feeling like a calculator wearing office clothes.

Method 1: Change the Cell Format to Show Only the Date

The fastest way to remove the visible time from a date in Excel is to change the cell format. This is perfect when the time does not bother your calculations and you simply want the worksheet to look cleaner.

Best for: Quick visual cleanup

Use this method when you want the timestamp to disappear from view but do not need to erase it from the underlying value.

Steps

1. Select the cells that contain the date and time.
2. Press Ctrl + 1 to open the Format Cells dialog box.
3. Choose the Number tab.
4. Select Date from the category list.
5. Pick a date format such as 3/14/12, March 14, 2012, or another style without time.
6. Click OK.

You can also use a custom format such as:

or:

For example, if cell A2 contains:

changing the format to mm/dd/yyyy will display:

Here is the important catch: formatting only changes what you see. The time is still stored in the cell. If you use the value in formulas, comparisons, or pivot tables, the hidden time may still affect results. This is why formatting is the spreadsheet equivalent of sweeping crumbs under the rug. It looks great, but the crumbs are still having a tiny party.

Method 2: Use the INT Function to Remove the Time

If you want to truly remove the time from a date-time value, the INT function is one of the cleanest options. Since Excel stores the time as the decimal part of a date-time number, INT keeps the whole number and removes the decimal.

Best for: Creating clean date values for formulas, filters, and pivot tables

Use INT when the values are real Excel date-time values, not text strings.

Formula

If A2 contains:

then =INT(A2) returns the date value for:

Steps

1. Insert a new column beside your date-time column.
2. In the first new cell, enter =INT(A2).
3. Copy the formula down the column.
4. Format the result column as a date if Excel displays a serial number.
5. Copy the new column and use Paste Special > Values if you want to replace formulas with fixed dates.

The INT method is a favorite because it is short, memorable, and wonderfully bossy. It tells Excel, “Keep the date. Lose the time. No speeches.”

When INT Can Be Tricky

For normal modern dates, INT works beautifully. However, remember that INT rounds down to the nearest integer. This matters mostly with negative numbers, which are uncommon in everyday date cleanup unless you are working with very old date systems or unusual imported data. For regular business spreadsheets, INT is usually the go-to choice.

Method 3: Use TRUNC to Cut Off the Time

The TRUNC function is another excellent way to remove the time from a date in Excel. Like INT, it removes the decimal part of a number. The difference is that TRUNC simply cuts off the decimal portion rather than rounding down.

Best for: A clear, precise “remove the decimal” formula

Use TRUNC when you want your formula to communicate exactly what is happening: the fractional time part is being chopped off.

Formula

If A2 contains:

then the result becomes:

Steps

1. Create a helper column next to the original timestamp data.
2. Enter =TRUNC(A2) in the first row.
3. Fill the formula down.
4. Apply a date format to the results.
5. Use Paste Special > Values if you want permanent date-only values.

TRUNC and INT will give the same result for typical positive Excel date serial numbers. Many Excel users choose INT because it is shorter; others prefer TRUNC because it describes the action more clearly. This is the spreadsheet version of choosing between coffee and tea. Both can get the job done, but people will still have opinions.

Method 4: Split Date and Time with Text to Columns

Text to Columns is a great tool when your date and time are stored together as text, especially after importing data from a CSV file, web form, scheduling system, or database export. If the date and time are separated by a space, Excel can split them into separate columns.

Best for: Imported text timestamps

Use this method when your values look like dates but behave like text, or when formulas are not giving the expected result.

Example

Suppose column A contains:

You can split the date into one column and the time into another.

Steps

1. Select the column that contains the date-time values.
2. Go to the Data tab.
3. Click Text to Columns.
4. Choose Delimited, then click Next.
5. Select Space as the delimiter.
6. Choose a destination cell so you do not overwrite important data.
7. Click Finish.
8. Keep the date column and delete or ignore the time column.

This method is wonderfully practical, but it has one big warning: always test it on a copy of your data first. Text to Columns can overwrite neighboring columns if you do not choose the destination carefully. Excel will not always shout before stepping on your data’s toes.

When to Use DATEVALUE After Splitting

If the date remains as text after splitting, use DATEVALUE to convert it into a true Excel date. For example:

Then format the result as a date. This is useful when you need to sort, filter, calculate age, group by month, or build date-based dashboards.

Method 5: Use Power Query for Repeatable Cleanup

Power Query is the best option when you repeatedly receive files with date-time columns and need the same cleanup every week, month, or reporting cycle. Instead of manually fixing timestamps again and again, you build the cleanup steps once and refresh them later.

Best for: Recurring reports and large datasets

Use Power Query when you are cleaning hundreds or thousands of rows, importing from external files, or creating a repeatable workflow.

Steps

1. Select your dataset.
2. Go to Data > From Table/Range.
3. Confirm that your data has headers, if applicable.
4. In Power Query, select the date-time column.
5. Change the data type to Date, or split the column by delimiter if the timestamp is text.
6. Click Close & Load to return the cleaned data to Excel.

Power Query is especially helpful when your timestamp column is messy but consistent. For example, if every row follows the pattern 2026-06-03 14:47:18, Power Query can split by the space or convert the entire column to a date type. The result is cleaner, more reliable, and easier to refresh than a maze of formulas.

Think of Power Query as hiring a very patient assistant who never complains about doing the same cleanup task for the 47th time. Honestly, we should all be so emotionally regulated.

Bonus Option: Find and Replace for Simple Text Timestamps

Find and Replace can remove time when your date-time entries are text and the time always appears after a space. It is not always the safest method, but it can be fast for simple cases.

Example

If your cells contain:

you can search for everything after the space and replace it with nothing.

Steps

1. Select the cells.
2. Press Ctrl + H.
3. In Find what, type a space followed by an asterisk: *
4. Leave Replace with blank.
5. Click Replace All.

This tells Excel to find the first space and everything after it. However, be careful. If your date text contains spaces, such as June 3, 2026 2:47 PM, this method can cause chaos with the confidence of a raccoon in a pantry. Use it only when your date format is predictable.

Which Method Should You Choose?

The best method depends on your goal. If you only need the worksheet to look clean, use formatting. If you need true date-only values, use INT or TRUNC. If your timestamps are text, try Text to Columns or DATEVALUE. If the cleanup repeats often, Power Query is the grown-up choice with a clipboard and a sensible calendar.

Common Problems When Removing Time from Dates in Excel

The date turns into a number

If Excel displays a number like 46176, do not panic. That is the date serial number. Format the cell as a date using Ctrl + 1, then choose a date format.

The formula returns an error

Your timestamp may be stored as text. Try using Text to Columns, DATEVALUE, or Power Query to convert it into a real date.

The time disappears but pivot tables still split the same day

You probably used formatting only. The time is hidden, not removed. Use INT or TRUNC, paste values, and build the pivot table from the cleaned column.

Dates sort alphabetically instead of chronologically

This usually means the dates are text. Convert them to real dates before sorting. A real Excel date can be used in calculations; a text date just stands there looking date-like and causing drama.

Practical Example: Cleaning a Sales Export

Imagine you export sales data from an online store. Column A contains order timestamps:

You want daily sales totals. If you group by the original timestamp, Excel may treat each time as a separate value. That is not helpful unless your report goal is “How many ways can one date annoy me?”

Create a new column called Order Date and enter:

Copy it down, format it as a date, then paste values if needed. Now all orders from June 1 share the same date value, and your pivot table can group daily totals correctly.

My Experience: What Actually Works Best in Real Excel Cleanup

In real spreadsheet work, removing time from a date is rarely the dramatic part of the project. The dramatic part is discovering the timestamp problem after your summary report looks wrong and everyone is already waiting for the file. I have seen this happen most often with exported data from CRMs, accounting tools, email platforms, scheduling apps, and e-commerce systems. The export includes exact timestamps because the system needs them, but the person building the report only needs the date.

The first lesson is simple: do not trust what you see in the cell. A column may display 06/03/2026, but the formula bar may reveal 06/03/2026 2:47:18 PM. That hidden time can make lookups fail, make duplicate checks confusing, and cause pivot tables to group records in unexpected ways. Before cleaning anything, click a few cells and check the formula bar. It is like checking under the couch before guests arrive. You may find nothing. You may find a whole situation.

For one-time cleanup, I usually reach for INT. It is quick, easy to explain, and works well with true Excel date-time values. I create a helper column, apply =INT(A2), copy down, format the results, then paste values. This gives me a clean date column that behaves properly in filters, formulas, and pivot tables. If I am sending the file to someone else, I prefer pasted values over live formulas because it reduces the chance that someone deletes the source column and breaks the workbook.

For imported CSV files, Text to Columns can be faster, especially when the timestamp is text. The key is to duplicate the column first. That way, if the split goes sideways, you still have the original data. I also make sure there is an empty destination column before running the wizard. Text to Columns is powerful, but it has the subtle personality of a bulldozer. It does exactly what you tell it, not necessarily what you meant.

For recurring reports, Power Query is the better long-term answer. If you download the same type of file every Monday, do not manually clean timestamps every Monday. Build a Power Query process once, set the column type to Date, and refresh when the new file arrives. This is especially useful for dashboards, monthly reports, and team workflows where consistency matters. Manual cleanup feels fast the first time and suspiciously medieval by the tenth time.

Another practical habit is naming the cleaned column clearly. Use labels like Order Date Clean, Date Only, or Report Date. Avoid vague headers like Date 2, because future you will open the workbook three weeks later and wonder who allowed this tiny mystery novel into the spreadsheet.

Finally, always test before replacing original data. Filter a few dates, compare row counts, and check whether formulas still work. Removing time from Excel dates is simple, but clean reporting depends on tiny details. A timestamp hiding in a date column can quietly distort totals, delay reports, and make your spreadsheet look guilty. Clean it once, clean it correctly, and your workbook will feel lighter, tidier, and far less haunted.

Conclusion

Removing the time from a date in Excel is easy once you know whether you need a visual fix or a true data cleanup. Formatting is perfect for presentation, but it does not remove the underlying time. The INT and TRUNC functions are ideal for creating real date-only values. Text to Columns and Find and Replace help with text-based timestamps, while Power Query is the smartest choice for repeatable cleanup.

The best all-purpose approach is simple: use =INT(A2), format the result as a date, and paste values when you need a permanent clean column. Your reports will sort better, your pivot tables will behave, and your spreadsheet will stop pretending every date needs a dramatic timestamp entrance.