How to Remove Time from Dates and Reformat to YYYY-MM-DD in Excel
Question details
The user needs to bulk remove timestamp data from date cells and display the remaining dates in the yyyy-mm-dd format without manual editing.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up raw data exports where dates contain unwanted time components across thousands of rows.
- Observed behavior
- Time values are attached to dates, preventing clean formatting. Applying cell formatting alone only hides the time component but does not remove the underlying time data.
Determine if your date cells are stored as true date-time values (numbers) or as text strings, as this dictates the extraction method you should use.
Remove Time using the INT Function
Best for actual date-time values, as Excel stores dates as integers and times as decimal fractions.
In Excel, a true date is stored as a whole number representing the number of days since January 1, 1900. Time is stored as a decimal portion of that day. By using the INT function, you can strip away the decimal (the time) and keep only the whole number (the date).
Add a new blank column directly next to your existing date-time data.
Type =INT(A1) in the first cell of the new column (assuming your date is in cell A1) and press Enter. This extracts only the integer part, effectively removing the time.
Double-click the fill handle at the bottom-right corner of the cell to apply the formula down to the rest of the rows in your dataset.
Select the entire new column, press Ctrl+C to copy it, right-click the same selection, and choose 'Paste as Values' (the clipboard icon with 123) to remove the formulas and keep the static date numbers.
Keep the column selected, press Ctrl+1 to open the Format Cells dialog, navigate to the 'Custom' category, type yyyy-mm-dd in the Type box, and click OK.
Extract Dates using Text Functions
Use this method if your timestamps are stored as text rather than numerical date values.
Easily Clean and Reformat Data in WPS Spreadsheet
WPS Spreadsheet offers powerful data processing tools, including advanced formulas and custom cell formatting, to help you clean up date and time data across thousands of rows effortlessly.
- 1. Open your dataset: Launch WPS Spreadsheet and open your .xlsx file containing the mixed date-time data.
- 2. Apply the formula: Use the =INT() function in an adjacent helper column to strip out the decimal time values seamlessly.
- 3. Format instantly: Right-click the cleaned data, select 'Format Cells', and apply your desired 'yyyy-mm-dd' custom format directly.
Frequently Asked Questions
Why doesn't changing the date format remove the time in Excel?
Changing the cell format only alters how the data is displayed visually on your screen. The underlying value still contains the time (stored as a decimal fraction). To completely remove the time for accurate sorting, filtering, or comparison, you must use functions like INT or the Text to Columns tool.
Can I remove timestamps using the Find and Replace feature?
Yes, if your dates and times are stored as text and separated by a space (e.g., '2023-10-25 14:30'). You can press Ctrl+H to open Find and Replace. In the 'Find what' box, type a space followed by an asterisk (' *'). Leave the 'Replace with' box empty, and click Replace All. This deletes everything after the space.
What should I do if the INT formula returns a #VALUE! error?
If the INT formula returns a #VALUE! error, it means your original date is stored as text rather than a numerical value. You will first need to convert the text to a recognized date using the DATEVALUE function or by using the Data > Text to Columns wizard before attempting to remove the time.
