To extract the date from an Excel date-time value in cell A2, enter =INT(A2). The formula removes the time portion and returns the date as an Excel date value; apply a date format if the result appears as a number.
What Excel stores in a date-time value
Excel normally stores a date-time as a number: the whole-number part represents the date, and the digits after the decimal point represent the time of day. For example, 6:00 AM is one quarter of a day, or 0.25. A date-time such as March 8, 2025 at 6:00 AM therefore has the same whole-number date component as March 8, 2025, plus a fractional time component.
This is why INT is a convenient way to separate the date. It keeps the whole-number portion and discards the fraction. The cell may show a serial number after you enter the formula, but that does not mean the calculation failed: Excel is displaying the numeric value without a date format. The underlying result is still suitable for date calculations once formatted as a date.
Excel dates do not carry a time zone. A value that looks like a date and time is just a serial number, not a record of whether the time was entered in UTC or local time. If an imported timestamp needs a time-zone conversion, do that conversion before extracting the date. Otherwise, a timestamp near midnight could belong to a different calendar date in the intended time zone.
The quickest formula: INT
With a real Excel date-time in A2, enter this in another cell:
With the date-time value in A2, use =INT(A2). For example, if A2 represents March 8, 2025 at 2:25 PM, the result is the date value for March 8, 2025.
Copy the formula down to process other rows. You can drag the fill handle, double-click it to fill alongside neighboring data, or copy and paste the formula. If the result shows a number, select the result cells and choose Home > Number Format > Short Date. You can also open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, then choose a date format. The exact date style available depends on the regional settings and formats in the workbook.
INT is especially useful when the source is numeric and you want the extracted date to remain a real date. It can be sorted chronologically, used in date arithmetic, grouped in many PivotTable scenarios, and compared with other date values. If the cell is blank, INT returns zero, which may display as a date depending on the cell format; handle blanks separately if that would be confusing.
Other formula options and when to use them
For a genuine Excel date-time, another option is to rebuild the date from its year, month, and day components. With the date-time in A2, use =DATE(YEAR(A2),MONTH(A2),DAY(A2)). It returns the same calendar date while omitting the time. This can make the intent explicit in a complex worksheet, but it is longer than INT and usually offers no advantage for ordinary positive Excel date-time values.
If you need a date displayed in a specific text format for a label, file name, or export, TEXT can produce one. With the date-time in A2, use =TEXT(A2,"yyyy-mm-dd"). The result for March 8, 2025 is the text 2025-03-08. The format code uses yyyy for the four-digit year, mm for the month, and dd for the day.
TEXT is not the best choice when the result must remain a date. It returns text, which may not sort or calculate like a date value in every context. Use INT and apply cell formatting for analysis; use TEXT when a fixed-looking string is the intended final output. A common misconception is that displaying only a date removes the time. Formatting changes what is shown, while INT changes the stored result.
Formatting a cell is not the same as extracting a date
If you only want to hide the time in a report, you may not need a formula. Select the original date-time cells and choose Home > Number Format > Short Date, or select a suitable date format in Format Cells. The cells will display dates without the time, but their underlying values still contain the time fraction.
That distinction matters in comparisons and calculations. Two values can appear to show the same date but differ because one has a time. For instance, a date-time at 9:00 AM is greater than the date value for midnight on that date. A test for exact equality with a date-only value may therefore fail unless you first remove the time or compare date portions. Conversely, keeping the time is useful when you need to calculate elapsed hours or order events within a day.
Choose formatting alone when the time is irrelevant to the viewer but still useful in the source data. Choose a formula when later steps need a date-only value, or when you want to preserve the original timestamp in one column and create a separate date field in another.
Extracting a date when the source is text
INT works on numeric date-time values, not on every string that merely looks like one. Imported data can contain text such as 2025-03-08 14:25. If Excel has stored that value as text, INT may return an error because it cannot take the integer portion of a text string.
For text that consistently begins with an ISO-style year-month-day date, with the date in A2 as yyyy-mm-dd followed by any time, you can build a date from the first ten characters:
With fixed-format text in A2 beginning yyyy-mm-dd, use =DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2)). For 2025-03-08 14:25, the result is March 8, 2025.
This approach relies on the text having that exact character layout. It is not a general parser for every date style. For text dates written in a consistent format recognized by your Excel regional settings, DATEVALUE can also convert a date string to a date value. For example, with a date and time string in A2 whose first ten characters are recognized by Excel, use =DATEVALUE(LEFT(A2,10)). DATEVALUE interprets text according to supported formats and regional settings, so the same ambiguous string can be read differently on different computers.
For larger imports, consider converting the source column to a real date-time before applying INT. In Excel for Microsoft 365 on Windows, select the data and use Data > Text to Columns when the file has a consistent delimiter or date layout. Review the preview and column data format before finishing, because choosing the wrong date order can silently change how a value is interpreted. After conversion, confirm that the cells behave as dates—for example, apply a date format and check that date arithmetic works.
Use Power Query for repeatable imports
If you regularly receive a file with date-time values and need a date-only column every time, Power Query can make the cleanup repeatable. In Excel for Microsoft 365 on Windows, load or select the source table, then choose Data > From Table/Range if it is not already in a query. In the Power Query Editor, select the date-time column and choose Transform > Date > Date Only. Then choose Home > Close & Load to return the transformed data to Excel.
Power Query is useful when the same steps must be reapplied after refreshing a file or table. It transforms the query output rather than changing the original source file. Check the column type and preview before loading, especially if the incoming values are text or use a different date order. A locale mismatch can cause a valid-looking string to be interpreted incorrectly.
Excel for the web supports many workbook and query tasks, but available data connections and editing options can differ from desktop Excel. If you do not see the relevant Power Query commands in the web interface, use the Windows desktop app to create or adjust the transformation. On Mac, Power Query availability and connection support can differ by Excel release, so check the commands available in that installation.
Common errors and practical checks
- The result is a number. This is usually a formatting issue, not a formula error. Apply a date format to the result cell.
- INT returns an error. The source may be text, contain an error, or be an unexpected value. Check the cell’s contents and convert valid text to a real date-time first.
- The date is one day off. Check whether the source timestamp is in a different time zone, whether the import interpreted month and day in the wrong order, or whether the date was converted before extraction.
- The formula seems to leave the time in place. Confirm you are viewing the formula result, not the original column, and that the result cell is not using a custom display that includes a time.
- A blank source displays as an old date. INT of a blank reference evaluates to zero. Use a blank check if you want the output cell to remain blank.
To keep the output blank when A2 is blank, use =IF(A2="","",INT(A2)). This is suitable when blank cells are genuinely empty. A cell containing a space or a formula that returns an empty string can behave differently from a numeric date-time, so inspect the source if the result is unexpected.
For a quick quality check, compare a few extracted results against the original values with the time visible. Also test values just after midnight and near the end of the day. These are the rows most likely to reveal a time-zone or date-parsing issue. Keep the original timestamp column until you have confirmed the cleaned results.
How to choose the right method
Use INT for the usual case: a valid Excel date-time stored as a number, with a date-only value needed for formulas, sorting, or analysis. Use cell formatting alone if you only want to hide times in a display and want to retain the original timestamps. Use TEXT if you explicitly need date characters in a fixed format, and remember that the result is text. If the source is text, convert it using a method that matches its exact format; for recurring imports, consider Power Query.
Keep both source and result columns when accuracy matters. Label them clearly, such as Timestamp and Date, so another person can tell whether the time was removed or merely hidden. If you plan to export the workbook, test the exported file or destination system: date values and text strings may be interpreted differently outside Excel.
Frequently Asked Questions
How do I remove the time from a date in Excel?
With a real date-time value in A2, enter =INT(A2) in another cell and format the result as a date. The formula removes the time fraction while preserving a usable Excel date value.
Can I extract the date without a formula?
Yes, if you only need to hide the time. Apply a date format to the original cells. This changes the display, not the underlying date-time value, so use a formula if later calculations need the time removed.
Why does INT show a five-digit number?
Excel is displaying the date serial number using a General or Number format. Select the result and apply Short Date or another date format. The serial number is Excel’s numeric representation of the date.
Does TEXT return a real date?
No. TEXT returns text formatted to look like a date. It is useful for labels and fixed-format exports, but use INT or DATE when you need a date value for calculations or date-based sorting.
What if my date-time is stored as text?
Convert it using a method that matches the source format. For fixed yyyy-mm-dd text, DATE with LEFT and MID can build a date; DATEVALUE may work for formats recognized by your regional settings. Verify the result before relying on it.
Related
- Excel Attendance Format: A Clear Monthly Attendance Tracker
- Excel Attendance File to Print: Set Up and Print It
- Excel Timesheet Format: Build a Reliable Weekly Sheet
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Difference Between Two Numbers: Formula GuideTo find the signed difference between two numbers in Excel, subtract one cell from the other, such as…
- VLOOKUP in Excel: Steps, Examples, and Common FixesTo use VLOOKUP in Excel, put the value you want to find in a cell, then enter a…
- How to Group the Same Numbers in Excel: Sort and CountTo group identical numbers in Excel, sort the entire data range by the column containing those numbers: select…
- XLOOKUP Practice Examples for Everyday Excel TasksFor an exact-match lookup, put the value to find in one cell, then use XLOOKUP to search a…
- Concat Excel Different Sheets: Formulas and ExamplesTo concatenate values from different Excel sheets, use CONCAT with a reference to each sheet, such as =CONCAT(Jan!A2,Feb!A2).…
- Concat Excel Columns: Formulas, Methods, and Fixes ExplainedTo concat Excel columns—that is, join values from separate cells into one—use the ampersand operator. With first names…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.