How to Remove the Time From a Date in Excel

To remove the time from a date in Excel, wrap the cell in INT, as in =INT(A2), and format the result as a date. That drops the time for real. A date format alone only hides the time, which looks the same but still breaks counts and lookups. This guide explains why, shows the formula and no-formula options, and covers how to count timestamps by day.

Why a date in Excel can carry a hidden time#

Excel stores every date as a whole number called a serial number: a day count in which 1 January 1900 is day 1. A time is stored as a fraction of a day: 0.5 is noon, 0.375 is 9:00 AM, 0.75 is 6:00 PM. A date with a time is the two added together.

So "removing the time" means removing the decimal part. Exports from booking systems, tills and web forms usually include a time, even when you only care about the day. The article on why Excel dates are numbers explains the serial number system in more detail.

How to remove the time with INT#

INT rounds a number down to the nearest whole number, which keeps the date and throws away the time. With timestamps in column A:

1. In B2, type =INT(A2) and press Enter.
2. Fill it down (double-click the fill handle, or select the range and press Ctrl+D; Mac: ⌘D).
3. If the result shows as a number like 46300, or still shows 12:00 AM, format it: select column B, then Home > Number Format > Short Date.

=INT(A2) turns 46300.375 into 46300, which displays as 5 Oct 2026. TRUNC gives the same result for dates: =TRUNC(A2) also returns 46300. INT and TRUNC only differ on negative numbers, which dates never are; see INT vs TRUNC for the details. A third option, =A2-MOD(A2,1), subtracts the time part directly. MOD returns the remainder after division, so MOD(A2,1) is the time alone (0.375). The MOD and INT guide shows more tricks like this.

To replace the original column with the clean dates, copy column B, then paste it over column A as values (Home > Paste > Paste Values) and delete the helper column.

Use TEXT when you want a date label#

TEXT turns a number into text in any format you choose:

=TEXT(A2,"dd/mm/yyyy")

This returns the text 05/10/2026, with no time. That is useful for a label, a report heading or an email line, such as ="Orders on "&TEXT(A2,"d mmm yyyy"). But the result is text, not a date. You can't add days to it, and it won't match real dates in a lookup. Use INT when you need a date that still works in formulas. The TEXT function guide lists the format codes.

Hide the time with a number format#

Sometimes you only want the time out of sight. Changing the number format does that without touching the value:

1. Select the cells.
2. Press Ctrl+1 (Mac: ⌘1) to open Format Cells.
3. Choose Date and a type with no time, or choose Custom and type dd/mm/yyyy.

The cell now shows 05/10/2026, but the formula bar still shows the time, and the value is still 46300.375. That's the trap: everything looks like a plain date, but formulas see the time. The guide to changing the date format covers the format codes.

Count or look up dates that still have times#

If your data keeps its times, a formula that checks for a plain date finds nothing. Take these order times, with the date 5 Oct 2026 in F1:

=COUNTIF(A2:A7,F1) returns 0, because 46300.375 is not equal to 46300. Count the whole day instead: everything from the start of 5 Oct up to, but not including, the start of 6 Oct.

=COUNTIFS(A2:A7,">="&F1,A2:A7,"<"&F1+1)

The result is 3 (S-501, S-502 and S-504). Notice S-506: it is exactly midnight on 6 Oct, so "<"&F1+1 correctly leaves it out. The same pattern works in SUMIFS.

Another option is =SUMPRODUCT(--(INT(A2:A7)=F1)), which strips the time from every cell before comparing; it also returns 3. For lookups, the guide to looking up a value by date shows how to match a date in data with times.

Remove the time in place without formulas#

If you don't want a helper column, Find and Replace can strip the time from date-time cells directly. Make a copy of the data first, because this changes the original values:

1. Select the date-time cells.
2. Press Ctrl+H (Mac: Control+H) to open Replace.
3. In Find what, type a space and an asterisk: *.
4. Leave Replace with empty and click Replace All.

This deletes everything after the first space in the cell's contents, which is the time. Excel then reads what's left as a plain date. It depends on how your dates are entered and on regional settings, so check a few cells afterwards: the formula bar should show no time. The INT method is safer when you're unsure.

Practise it#

Date questions in job tests often hide a time inside the data to see if you notice. Practise date basics in a live spreadsheet: you type the formula and it checks it instantly. Then practise number and date formats in the more formatting lesson, or try the exercise on this page first.