Timestamps in Excel typically contain both the date and the time. We often, however, disregard the time and only use the dates in timestamps.
In this tutorial, I will show you varying ways you can do to remove the time – both temporarily and permanently.
Before I give a breakdown of the various methods to do this, I have a little trivia for you.
Good to Know
Did you know that in Excel, dates and times are stored as numbers?
You will see this when you change the format of cells containing dates into General (as shown below).
The integers represent the date, while the decimals represent the time.
This little information will help you deal with date and time values later on.
Table of Contents
1. Changing the Cell Format
Use this method if your goal is to hide the time from the cells and not actually remove it, per se.
1. Select all the cells containing the timestamps.
2. From the Home menu, click on the small arrow on the right side of the Number section.
This will open the Format Cells menu.
3. Select a date format that excludes time.
Once selected, click OK.
4. That’s it! The cells will now display just the date.
Note that when you click on one of these cells, the time will appear in the formula bar.
Important Notes:
- Note that this method does not permanently remove the time but only hides it from plain sight.
- When you click on one of these cells and view the formula bar, you will see that the time is still there.
- If you use these cells as references to formulas, remember that Excel treats them as date and time.
2. Using a formula
If your goal is to extract the date from the timestamp, then this next option is for you.
Note that this approach requires you to have a separate column for the formulas. If you prefer working only on the column that has timestamps, please proceed to the next option (Find and Replace).
There are four Excel functions available that can extract date. The format of the result for each of them varies, so see what fits your needs.
INT() or TRUNC()
Both of these functions return the integer part of a cell. They disregard the decimal part.
As mentioned in the trivia above, the integer represents the date, while the decimal represents the time.
Therefore, when you use either of these functions, you can get the date value from the timestamp.
=INT (number)
=TRUNC (number, [num_digits])
Once you click enter, the date of the timestamp should appear.
Now, the result will depend on how your cells are formatted. If your cells have a timestamp format, you will see something like this:
Note that the time is still there, with 12:00 AM as its value — this is equivalent to a zero value for time.
If your cell is in General format, you’ll see a whole number appear (like the image below).
If this is the case, you’ll need to change the format of these cells with the appropriate date format.
To do these, highlight all the cells and press CTRL + 1 (this is a shortcut to opening the Cell Format menu). Select the appropriate date format and click OK. You should now see your cells with just the date.
In case you’re wondering, these two functions are practically the same, except that the INT() function rounds a number down to the nearest integer.
The TRUNC() function, on the other hand, truncates a number to an integer by removing the decimal (without rounding it off).
They may differ in the way that they handle the decimals, but it doesn’t really matter if you use them to extract the dates.
TEXT()
The TEXT() function converts a value to a text in a specific number format.
It uses this syntax: =TEXT (value, format_text)
- value
- refers to the value or cell to be formatted
- format_text
- refers to how the value will be formatted
- since we are working on dates, we will use:
- m – for month
- d – for day
- y – for year
NOTE: This function converts the timestamp into text. You will not be able to perform date calculations on these cells unless you first revert them to date format.
If you’re not concerned about converting them to actual dates, you can stop here.
But if you want to convert day, month or year as dates, you will need to add the DATEVALUE() function.
DATEVALUE()
The DATEVALUE() function converts a date in text form to a number that represents the date in Microsoft Excel date-time code.
It uses this syntax: =DATEVALUE (date_text)
Since we already have the text format of the date after adding the TEXT() function, we only need to add DATEVALUE() in our formula.
It should look something like this:
3. Using Find and Replace
This last option is great if you’re not much of a fan of formulas and would prefer to directly apply the changes to the cells containing timestamps.
Note that this method will only work if your timestamp is formatted like this: [date] [space] [time]
Example: 2/4/2028 4:46:53 AM
** The space between the date and time is required.
1. Select all cells containing the timestamps.
2. Press CTRL + H to open the Find and Replace menu.
In the Find What textbox, type a space followed by an asterisk “ *”
Leave the Replace textbox blank and click on the Replace All button.
3. Notice that all the time values in your cells are now 12:00 AM — this is the zero equivalent of time.
4. Close the Find and Replace menu.
5. Change the format of the cells by pressing CTRL + 1 and selecting the appropriate date format.
6. Once that is set, you should now only have the dates inside your cells.
Conclusion
As you have seen in the examples above, removing the time from the date or timestamps in Excel is not that hard. Various options are available, and you can decide which best meets your needs.