Removing the time from a date in Excel may seem daunting, but it’s quite straightforward. By using a simple formula or the formatting options, you can easily extract just the date portion from a cell that contains both date and time. Let’s dive in and learn how to accomplish this task.
Step by Step Tutorial on How to Remove Time from Date in Excel
Before we jump into the steps, let’s understand what we’re aiming to do. In Excel, dates and times are stored as numbers. The integer portion of this number represents the date, while the decimal portion represents the time. Our goal here is to strip away the time portion so that we’re left with just the date.
Step 1: Use the INT Function
Enter the formula =INT(cell) into a new cell, replacing “cell” with the reference to the cell that contains the date and time.
The INT function rounds down a number to the nearest integer. Since the integer part of the date-time number represents the date, using the INT function will give us just the date without the time.
Step 2: Format the Cell as a Date
After applying the INT function, change the cell format to “Date” to display it in a date format.
Right-click the cell, select “Format Cells,” then choose “Date” from the category list. This will ensure that the cell displays the value as a date, not a number.
Step 3: Copy the Formula Down
If you have multiple cells from which you want to remove the time, drag the formula down to apply it to the entire column.
Click the fill handle at the bottom right corner of the cell with the formula and drag it down to fill the other cells. This will apply the INT function to each cell and remove the time from each date-time entry.
After completing these steps, you will have a column of cells that display only the date, with the time portion removed. This can be particularly useful when you need to sort or filter by date without the time affecting the results.
Tips for Removing Time from Date in Excel
- If you need a permanent solution, use “Paste Special” to paste the values after using the INT function.
- To remove time from multiple cells at once, select the range and apply the INT function using an array formula.
- If you’re not comfortable with formulas, you can also remove time by formatting the cells and setting the time format to “None.”
- Keyboard shortcut lovers can use Ctrl+1 to quickly open the “Format Cells” dialog box.
- Remember to check for cells with dates that may already be formatted as text, as the INT function might not work on them directly.
Frequently Asked Questions
What is the INT function and how does it help in removing time from dates?
The INT function rounds down a number to the nearest integer, effectively removing the decimal part that represents the time in a date-time value.
Can I use this method to remove dates and keep only the time?
No, this method is specifically for removing the time portion. To keep only the time, you would use a different approach, like subtracting the INT of a cell from the cell itself.
Will this method change the original data?
Using the INT function in a separate cell will not alter the original data. However, if you convert the original cell using “Paste Special,” it will overwrite the original date-time values with just the dates.
What if my date-time values are formatted as text?
If your values are formatted as text, you’ll need to convert them to a date-time serial number first before using the INT function.
Can I remove the time from date-time values in Excel Online or Excel for mobile devices?
Yes, the steps and functions are the same across all versions of Excel, including Excel Online and mobile apps.
Summary
- Use the INT function to remove the time from a date-time value.
- Format the cell to display the result as a date.
- Copy the formula to other cells if needed.
Conclusion
Mastering the art of how to remove time from date in Excel can significantly streamline your data management tasks. Whether you’re preparing reports, analyzing trends, or simply organizing your data, knowing how to extract just the date portion can be incredibly useful. With the simple steps outlined in this guide, you’ll be able to perform this action with ease, and the additional tips provided will help you handle any variations or issues that may arise. So go ahead, give it a try, and watch as your Excel proficiency grows. Remember, practice makes perfect, and with a bit of patience, you’ll soon be an Excel wizard!
Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.
After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.
His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.