Working with spreadsheets, I frequently encounter the challenge of cleaning up date and time data in Excel. It’s common to have both date and time combined in a timestamp, but sometimes you only need the date component for your analysis or report. The process of extracting just the date can seem daunting given the numerous formatting options Excel offers, but fortunately, there are straightforward methods to achieve this.
As an example, imagine you have a dataset where column A contains timestamps with both date and time, and your goal is to have column B show only the date. It’s crucial to understand that Excel represents dates and times as numbers internally, which is what allows various manipulations, including separating date from time. By grasping this concept, you can leverage Excel’s capabilities to modify the data as needed without affecting the underlying numeric representation of the date and time values.
Key Takeaways
- Excel treats dates and times as numeric values, enabling various formatting and manipulation options.
- Simple methods are available to strip time from date data, ensuring clarity in data presentation.
- These solutions are applicable across different versions of Excel on both Windows and Mac systems.
Change the Format to Hide the Time from Timestamp
When working with dates and times in Excel, there are occasions where I might need only the date displayed in a cell, without the accompanying time. I’ve found that it’s pretty simple to do this by modifying the cell’s format. It doesn’t delete the time from the timestamp—it just conceals the time information. Here’s how I do it:
First, I click on the cell with the timestamp. If I want to adjust multiple cells at once, I select the whole range containing the timestamps. Then, I press Ctrl+1 to bring up the Format Cells dialogue box, which is a handy shortcut that saves time.
In the Format Cells dialog box, I navigate to the Number tab. From there, I can see various categories for formatting, and I choose Date. Excel offers several predefined date formats, ranging from the short date like dd/mm/yy or mm/dd/yyyy, to more elaborate ones like dd-mmm-yyyy.
After selecting my preferred date format, all that’s left is to click OK. The time information instantly vanishes from the cells, showing only the date. Below is an example of what the cells look like before and after the format change:
It’s important to remember that the time data is still there, just not visible; the cell actually retains the full timestamp’s serial number, which includes both the date and time. This is particularly useful if later, for calculations or analytics, I need to tap into the exact time that events occurred.
This method to hide the time has a couple of advantages:
-
It’s very straightforward and doesn’t require any additional columns; I can simply apply the new format to the existing data.
-
The original timestamps remain untouched, giving me the flexibility to use the full datetime in other operations, if needed.
For those times when I want to scrub the time portion out completely, other methods are available, such as using formulas or tools like Find and Replace, or Text to Columns. But for quick, visual changes, altering cell formatting is my go-to solution.
Remove Time from Date in Excel Using Formulas
Using the INT Formula
When I need to strip off the time from a date and time stamp in Excel, I always find the INT function to be quite useful. What I do is simply apply =INT(A2)
to my data cell. This formula is great because it gets rid of the decimal part of my timestamp—since Excel stores time as a decimal value and the date as an integer value, removing decimals leaves just the date.
However, sometimes Excel may still display the time as 12:00 AM, which is a formatting issue. To just show the date, I follow these steps:
- Go to the Home tab.
- In the Number group, use the dropdown to select your desired format, usually the Short date or Long Date.
Excellent! The time is now removed from the date.
Using the DATE VALUE formula
Now, the DATEVALUE function is another trick in my Excel toolbox. It’s designed to pull the date value from a timestamp. It requires the date in text format, so if I just toss a cell or date at it, it’ll cough up an error.
What to do? Here’s a neat combo I use: =DATEVALUE(TEXT(A2,"dd-mm-yyyy"))
This crafty formula first transforms the date into text format with TEXT, and the DATEVALUE then translates it back, ignoring the time. Although this will show as a numeric value, I can easily tweak the cell’s format to display a date, or I can refine the formula to:
=TEXT(DATEVALUE(TEXT(A2,"dd-mm-yyyy")),"dd-mm-yyyy")
And there we have it. No more time—just the date in the text format I need.
Remove Time From Date using Find and Replace
Suppose I have a column of timestamps, but I only need the date part for my analysis. I can use Excel’s Find and Replace function to strip off the time component. Before I start, here’s what my data looks like:
07/24/2023 15:40
08/01/2023 09:30
...
Now, let’s break down the process:
- I select the cells containing the timestamp data.
- On the Home tab, I find the Editing group and click on Find & Select, then Replace.
- In the Find what: field of the dialog, I enter ” *” (that’s a space followed by an asterisk) to target the space-time format. Here’s how it looks:
- I make sure the Replace with: field is empty – we’re not replacing the time with anything. Like this:
- After I hit Replace All, the timestamps are left with just the date portion.
However, post-replacement, the cells might still look like they contain time because of Excel’s default date-time format (even if it’s just showing 12:00 AM). To convert this to a standard date format:
- I click the Home tab again.
- In the Number group, there’s a drop-down where I can select Short date or Long date, depending on my preference.
Using Find and Replace is super handy because I don’t need another column for the dates; I can overwrite my original data, but I always make sure to keep a backup – just in case. Also, this trick works only when the date and time are separated by a space. If there are leading spaces, I’d need to clear those out first.
It’s a simple, fast, and effective way to get just the dates when I don’t need the exact times cluttering my sheets. And by changing the cell format, I ensure my data looks clean and consistent throughout my spreadsheet.
I also make a note to myself that there are a bunch of neat tricks like these that can save me time. For example, I found this tip on how to convert seconds to minutes in Excel, which could be useful down the line. It’s always cool to find these shortcuts that make managing data just a bit easier.
Remove Time From Date using Text to Columns
I recently had a dataset with timestamp data, and I just needed the date part. So here’s what I did using Text to Columns in Excel:
-
Select your date column: I started by selecting the cells that had the timestamps I wanted to split.
-
Open Text to Columns: Next, I clicked on the Data tab and selected the ‘Text to Columns’ option.
-
Choose ‘Delimited’: In the wizard that popped up, I chose ‘Delimited’ to specify how I wanted to separate the data.
-
Set Space as delimiter: Afterwards, I picked ‘Space’ as the delimiter since this is typically what separates date from time in a timestamp.
-
Skip the time column: In the Data preview, I clicked on the time data’s column and chose ‘Do not import column (skip)’ to get rid of the time.
-
Hit Finish: I clicked Finish, and voila! I was left with the date only in the cells.
But, there was a slight catch. Despite removing the time, the default cell format may still show 12:00 AM or some other time. Here’s the fix:
- Change format to display date only: I went to the Home tab, clicked on the drop-down in the Number group, and selected Short date. This made sure only the date showed up.
And a cool thing about Text to Columns is that you can keep the original timestamp in one column and extract the date into another. Just specify a different destination cell in Step 3 of the wizard.
These nifty tricks remove time from date in Excel like a charm. It’s all about whether you want to keep the original timestamps intact or not. If yes, use formulas for a separate column. If no, the Text to Columns method alters data in place.
Exploring Excel is always fun, isn’t it? Check out these handy tutorials too:
- How to Calculate the Number of Days Between Two Dates in Excel
- How to Insert Timestamp in Excel
- Convert Date to Text in Excel
- How to use Excel DATEDIF Function
- Convert Time to Decimal Number in Excel (Hours, Minutes, Seconds)
- How to Stop Excel from Changing Numbers to Dates
- How to Convert Serial Numbers to Dates in Excel
- Combine Date and Time in Excel