Excel Convert Datetime to Date: Quick Steps

To convert a datetime value to just the date in Excel, use the formula =INT(A1) and format the cell as a date, or use =TEXT(A1,"mm/dd/yyyy") to get a text string. This article explains both methods, why they work, and how to handle common pitfalls like time zones and text dates.

How Excel Stores Dates and Times

Excel stores dates as serial numbers where 1 equals January 1, 1900, and each whole number adds one day. Times are fractional parts of a day: 0.5 is noon, 0.25 is 6:00 AM, and 0.75 is 6:00 PM. A datetime value like 45000.5 means the 45,000th day after January 1, 1900, at noon. When you see a date with a time in a cell, the underlying value is a decimal number; the display format just hides the fractional part.

This design means that removing the time is simply a matter of discarding the fractional part. The INT function rounds down to the nearest whole number, effectively stripping the time. For example, INT(45000.5) returns 45000, which formats as the date portion alone.

Method 1: Use INT or TRUNC

The most direct way to convert a datetime to a date is to use the INT function. This works because the integer part of the serial number is the date, and the decimal part is the time.

1. Select the cell or column containing the datetime values.
2. In a new column, enter =INT(A1) (assuming A1 is the first datetime).
3. Copy the formula down for all rows.
4. Select the new column, right-click, choose Format Cells, and pick a date format under the Number tab.

Alternatively, TRUNC works the same way for positive dates: =TRUNC(A1). For dates before 1900 (rare in Excel), INT behaves differently for negative numbers, but such dates are outside the normal Excel date range.

Method 2: Use TEXT to Get a Text Date

If you want the result as text rather than a serial number, use the TEXT function. For example:

=TEXT(A1,"mm/dd/yyyy") returns the date as text like “01/15/2023”. You can change the format code to suit your region, such as "dd-mmm-yyyy" or "yyyy-mm-dd".

Be aware that the result is text, not a real date. That means you cannot perform date arithmetic (like adding days) or sort chronologically as dates. Use this method only when you need a display string, for example, for concatenation in a report.

Method 3: Use Date Functions for Clarity

For maximum clarity, especially when sharing workbooks, you can extract the year, month, and day separately and reassemble them:

=DATE(YEAR(A1), MONTH(A1), DAY(A1))

This formula returns a true date value with the time set to midnight (00:00). It is more verbose but self-documenting. It also avoids any confusion about how INT handles negative numbers, though that is rarely an issue in practice.

How to Apply a Date Format to the Result

After converting, you must format the cell to show a date. Even if the formula returns a serial number, Excel might display it as a number like 45000. To change the display:

1. Select the cells with the converted values.
2. Press Ctrl+1 (Windows) or Cmd+1 (Mac) to open Format Cells.
3. Go to the Number tab, choose Date under Category, and pick a format (e.g., 3/14/2012).
4. Click OK.

If you prefer a custom format, choose Custom and enter something like yyyy-mm-dd.

Dealing with Text Dates and Time Zones

Sometimes datetime values are imported as text, such as “2023-01-15 14:30”. In that case, INT will not work because the cell contains text, not a number. You first need to convert the text to a real datetime using DATEVALUE and TIMEVALUE:

=DATEVALUE(LEFT(A1,10)) + TIMEVALUE(RIGHT(A1,8))

This returns a serial number, which you can then wrap with INT. For ISO format strings like “2023-01-15T14:30:00”, you may need to replace the “T” with a space first.

Time zones are another common issue. Excel does not store time zone information. If your datetime includes a zone offset (e.g., “2023-01-15 14:30 UTC”), you must decide whether to convert to your local time or strip the time before conversion. The INT function simply removes the time component; it does not adjust for time zones. If you need to convert to a specific time zone, add or subtract the offset in hours divided by 24 (since 1 hour = 1/24 of a day).

Common Mistakes and Pitfalls

• Forgetting to format the result – The formula may return a number, but it won’t look like a date until you format the cell.
• Using ROUND instead of INT – ROUND(A1,0) rounds to the nearest day, which can push a time like 23:59 to the next day. Use INT or TRUNC to always truncate.
• Applying INT to text – If the cell contains text, INT returns a #VALUE! error. Convert to a real datetime first.
• Confusing date serial with display – The underlying value remains a serial number; only the display changes.
• Copy-pasting values – If you copy the formula results and paste elsewhere, the original time might reappear if you paste as formulas. Use Paste Special > Values to keep only the date.

Alternatives: Power Query and Flash Fill

If you prefer a non-formula approach, Power Query can split a datetime column into a date column. In Excel, go to Data > From Table/Range, then in the Power Query editor, select the column and choose Date under the Data Type dropdown. This replaces the datetime with just the date (time truncated). Load the query back to a worksheet.

Flash Fill (Ctrl+E) can also work if you type the desired date in a column next to the data and press Ctrl+E. Excel will detect the pattern and fill the rest. However, Flash Fill is less reliable when the datetime format varies, so it’s best for consistent data.

Frequently Asked Questions

How do I convert datetime to date without losing the time?

You cannot keep both in the same cell; a cell holds one value. To preserve the time, copy the original column to another column and convert only the copy. Or use a formula to extract the date and time into separate columns: date with =INT(A1) and time with =MOD(A1,1).

Why does my date show as a number like 45000?

That is the serial number. The cell is formatted as General or Number. Select the cell, press Ctrl+1, and choose a Date format to display it as a date.

Can I convert a datetime to date in Excel for Mac?

Yes, all the methods work the same in Excel for Mac. The keyboard shortcuts are slightly different (Cmd+1 for Format Cells), but the formulas and steps are identical.

What if my datetime is in a different time zone?

Excel does not track time zones. If you need to convert to your local time, add or subtract the offset (in hours) divided by 24. For example, to convert UTC to Eastern Time (UTC-5), use =A1-5/24 before applying INT.

Is there a way to do this without a formula?

Yes, you can use Power Query or Flash Fill. Power Query is the most reliable for large datasets. Flash Fill is quick but can be inconsistent with varied formats.

Related

• Pixel Buds 2 Audio Cutout Issues: Fixes That Work
• Can LLMs Inspect Their Own Thoughts? Evidence
• Weather Lock Screen: How It Works & How to Set It Up

in dt | Datetime (Excel serial) | - | 45000.5 | 0
out d | Date (serial) | - | INT(dt) | 0