How to Remove Time from Date in Excel

Excel gives you plenty of flexibility to format and organise your cells however you see fit. For example, it provides numerous date formats to display important dates the exact way you want.

However, in some cases, these date values may come with the time. If you don’t want the time to accompany your dates in each cell, you may be wondering if there’s a way to remove time from date in Excel?

Well, thankfully, there’s not only one method to do this, but multiple. This guide is going to go over these methods and highlight why you may want to remove time from a date in the first place.

For more tips and advice about Excel, make sure to check out our instructor-led training courses!

Why You Might Need to Remove the Time Component

There are several reasons why you may not want the time to appear in a cell alongside a date. First, removing the time from the date cleans the data and simplifies it. This may make it easier to analyse, and also leads to less visual clutter, especially in large datasets that feature plenty of rows and columns.

When creating reports, you may only want to include the date, and having the exact time alongside every date may be distracting or lead to confusion. Also, in some cases, the time being inside a cell alongside the date may lead to calculation errors and other issues.

Download our Excel Formulas Cheat Sheet

Step-by-Step Guide to Removing Time from Date in Excel

Here’s a closer look at some of the main methods for removing time from date in Excel. For these two methods, make sure that your cells are formatted as dates, and not simply in Text or General formats.

Use the INT Function

If you want to remove time from date in one column, while still preserving the initial column that features both time and date in case you need it in the future, you can use the INT function.

1. Begin with your dataset that has date and time in the same cell, and create a column next to the date/time column.

2. Next, select the cell next to the first line and enter =INT(cell number) and hit Enter. In this example, that would be =INT(A2).

3. After hitting Enter, you’ll see the date, alongside a time value of zero. Don’t worry about that for now, as it’ll be removed in the next step. To apply this formula to all of the cells, simply drag it down the length of your second column.

4. To remove the 00:00 time value from this new column, simply select the range of the cells in your new column, navigate to the home tab, and change the format to either Short Date or Long Date.

5. After changing the format, you’ll see that only the date remains, with no time. As a result, you now have both a column with the time and date, and one with just the date.

In addition to using the INT function, you can also use the TRUNC function using the same steps and also achieve the same result.

Change the Format of the Cells Manually

If you don’t care about having two cells and simply want to visually remove time from the date in the original cell, the process is incredibly easy.

1. Begin with the same dataset as the previous method, but there’s no need to create a second column.

2. Next, simply select the entire range of cells, go to the Home tab, and change the format to Short Date or Long Date. Another option, instead of using the Home tab, is to select the entire range, right-click, select Format Cells, and choose your format from the menu that appears.

3. After confirming your selection, you're left with just the dates as the time has been visually removed.

If your date and time are formatted as text and not a date, you can use the DATEVALUE function to extract the date portion of the cell and convert it into a date value in Excel. To do this, simply add =DATEVALUE(cell number) into an empty cell, and it’ll return the date, without the time.

Troubleshooting Common Issues

As you attempt to remove time from date in Excel, there are some issues you may run into. Here are a few problems you may experience, and how to deal with them.

Incorrect formatting

If you’re experiencing a problem removing time from date, a likely culprit is incorrect formatting. For these methods to work, your cells need to be formatted in a certain way. So if you’re getting an error, check that the format of each cell in the dataset is exactly what it needs to be. If not, correct it, and the function should work as expected.

To prevent these issues altogether, consider checking formats before using functions to remove time from date to make sure they’re correct.

Function-related problems

Similarly, issues with your functions may be leading to difficulties in removing time from a date in Excel. Your functions and syntax need to be exact to operate as expected. Even a single incorrect cell reference or letter can lead to an error or display the wrong information.

As a result, always double-check the structure of your functions, and if you encounter an unexpected error, the syntax of your functions is one of the first places you look

Get in touch today to learn how we can help you and your team grow your business