How to remove the time from a date in Excel

Why the time is there even when you cannot see it

Excel stores every date as a whole number of days (October 5, 2026 is 46300) and every time as a fraction of a day (noon is 0.5). A date-time is both added together. The orders in column B came from a web shop export, so each one holds the exact moment it was placed, even though the column is formatted to show only the date.

That hidden decimal is what breaks things: a date-time is never equal to the plain date, so lookups, matches and SUMIFS on that day quietly skip it.

Method 1: INT keeps only the date

INT rounds a number down to the nearest whole number. For a date-time that removes the decimal, which is exactly the time. Put it in a helper column next to the original and fill it down.

TRUNC(B3) gives the same result for dates, since dates are never negative. Do not use ROUND: it rounds anything from noon onwards up to the next day, so WEB-5526 at 11:59 PM would land on October 7.

Excel may show the result as a number, or as a date with 0:00 on the end, depending on the format it picks up. Select the column and choose Home > Number Format > Short Date.

Formatting only hides the time

If you only need the sheet to look right for printing or a screenshot, a date format is enough. Pick a format without hours and the time disappears from view. The value does not change, so every formula that reads the cell still sees the time.

This is the trap behind most "my SUMIFS is missing rows" questions. Click a cell and look at the formula bar: if it shows 10/5/2026 1:47:00 PM, the time is still in there.

1. Select B2:B9.
2. Press Ctrl+1 to open Format Cells.
3. On the Number tab choose Date, then a type with no time, such as 3/14/2012.
4. Click OK. The cells now show only dates, but the stored values are unchanged.

SUMIFS and COUNTIFS that miss date-times

Say you want the total for October 6. Pointing SUMIFS at the date in F2 only matches cells that are exactly midnight on that day. Here that is the one order typed in as a plain date, WEB-5523. The three orders with a time are skipped.

=SUMIFS(D2:D9,B2:B9,F2) returns 46.5. The fix is to ask for "on or after midnight on the day, and before midnight on the next day". This works on the raw date-times, so you do not even need a helper column.

Same idea for counts and extra conditions

Swap SUMIFS for COUNTIFS to count orders per day, and add more range/criteria pairs as usual. Use "<" with F2+1 rather than "<=" with F2: "<=" stops at midnight on the 6th and misses everything after it.

If you would rather test the date part directly, =SUMPRODUCT((INT(B2:B9)=F2)*D2:D9) also returns 560.75 and works in every version of Excel.

Keep just the time instead

The other half of a date-time is the decimal. MOD(B3,1) returns the remainder after dividing by 1, which is the time of day. Format the cell as Time (h:mm) to read it. This is handy for "orders after 6 PM" checks or for splitting a date-time into two columns.

Date-times that came in as text

Some exports bring date-times in as text, like G2. Text sits left-aligned, ISNUMBER returns FALSE, and SUMIFS date criteria never match it. Convert it to a real date first.

For a whole column, Data > Text to Columns does it in place: choose Delimited, tick Space, set the first column to Date (YMD) and set the time column to "Do not import column (skip)". For a formula, =DATEVALUE(LEFT(G2,10)) works in Excel for ISO-style text, or build the date from its pieces as below. In Power Query, select the column and choose Transform > Date > Date Only.

Replace the originals with the dates

A helper column of INT formulas still depends on column B. To replace the date-times permanently, paste the results back as values.

1. In E2 enter =INT(B2) and fill it down to E9.
2. Copy E2:E9.
3. Select B2, then choose Home > Paste > Paste Values (or Ctrl+Alt+V, pick Values, OK).
4. Delete column E and format B as Short Date.