How to Remove Time from Date in Excel Using Formulas?

Do you have a spreadsheet in Microsoft Excel that contains date and time values, but you only need the date portion? Luckily, Excel provides several easy ways to remove the time part from a date using formulas. In this article, we’ll walk through the step-by-step process to extract just the date from a date/time value in Excel.

Understanding Date and Time Formats in Excel

Before we dive into the formulas to remove time from a date, it’s important to understand how Excel stores and displays dates and times:

• In Excel, dates are stored as sequential numbers starting from January 1, 1900 which is stored as the number 1. January 2, 1900 is stored as 2, and so on.
• Times are stored as decimal fractions of a 24 hour day. For example, 12:00 PM (noon) is stored as 0.5 because it is halfway through the day.
• When a cell contains both a date and time, Excel stores it as a decimal number where the integer part represents the date and the decimal part represents the time.

So for example, the date and time “6/15/2023 10:30 AM” is stored in Excel as the number 45100.4375.

How Excel Handles Time Zones and Daylight Saving Time

It’s worth noting that Excel doesn’t have any built-in concept of time zones or daylight saving time. It simply stores dates and times as numbers relative to the default date system (which starts on 1/1/1900).

However, you can use Excel’s time zone and daylight saving time features to display dates and times in different time zones or to adjust for daylight saving time:

• To change the time zone, go to File > Options > Advanced and scroll down to the “When calculating this workbook” section. Choose the desired time zone from the “Time zone” dropdown.
• To toggle daylight saving time on or off, go to File > Options > Advanced and check or uncheck the “Adjust daylight saving time automatically” box.

Keep in mind that these settings only affect how Excel displays dates and times, not how it stores them internally. The underlying date/time serial numbers remain unchanged.

Using the INT Function

One of the easiest ways to remove the time portion from a date/time value is by using the INT function. The INT function rounds a number down to the nearest integer. Since the date is stored as the integer part of the decimal number, using INT allows you to extract just the date.

Here’s how to use the INT function to remove time from a date in Excel:

1. Click on an empty cell where you want the date-only value
2. Type the formula =INT(A2) where A2 is the cell containing your original date/time value
3. Press Enter and the formula will return just the date portion

You can also copy this formula down to apply it to a column of date/time values.

For example:

The INT function works by rounding the decimal number down to the nearest whole number, effectively chopping off the time part.

Syntax

INT(number)

The number argument is the value you want to round down to an integer. It can be a cell reference or an actual number.

INT vs ROUNDDOWN

Excel has another similar function called ROUNDDOWN which also rounds a number down to a specified number of decimal places. However, there are a couple key differences between INT and ROUNDDOWN:

• INT always rounds down to an integer (whole number), whereas ROUNDDOWN can round to a specified number of decimal places.
• If given a negative number, INT rounds up towards zero while ROUNDDOWN rounds down away from zero.

For the specific purpose of removing times from dates, you can use either INT or ROUNDDOWN(number,0) and get the same result, since times are stored as positive fractions and you want 0 decimal places. But in general, they behave slightly differently.

Using the TRUNC Function

The TRUNC function (short for truncate) is very similar to INT. It also removes the decimal portion from a number. The difference is that TRUNC simply cuts off the decimal without rounding.

In most cases, INT and TRUNC will give the same result for removing time from a date, since times are always stored as positive decimal numbers.

To use TRUNC:

1. Select a blank cell
2. Enter the formula =TRUNC(A2) where A2 contains the date/time
3. Hit Enter to get the date without the time

For example:

Syntax

TRUNC(number, [num_digits])

• number is the value to truncate
• num_digits is optional and specifies how many decimal places to keep. For removing time, you can omit this.

TRUNC vs INT

While TRUNC and INT often give the same results, especially when dealing with positive numbers like date/time values, there is one key difference:

• INT rounds down to the nearest integer less than or equal to the given number. So negative numbers get rounded up towards zero.
• TRUNC simply cuts off the decimal part without any rounding. Negative numbers stay negative.

Here are some examples to illustrate:

So while both functions work for stripping times from dates, TRUNC is a bit more straightforward since it literally just truncates the decimal. INT does some rounding first.

In general, use TRUNC when you simply want to cut off the decimal part, and INT when you want the nearest integer less than or equal to the number.

Using Formatting

The above formulas work great when you need to use the date-only value in other formulas or calculations. But if you simply want to visually display the date without the time, you can use cell formatting instead.

Here’s how:

1. Select the cell(s) containing the date/time values you want to format
2. Right-click and choose Format Cells (or use the Ctrl+1 keyboard shortcut)
3. In the Format Cells dialog box, pick Date from the Category list
4. Choose the date format you want from the Type list on the right
5. Click OK to apply the formatting

The original cell value won’t change (it will still contain both date and time) but it will display visually as just the date.

You can also quickly apply date formatting using the keyboard shortcut Ctrl+Shift+#. This applies the default date format for your locale.

Changing the Default Date Format

If you don’t like Excel’s default date format, you can change it:

1. Go to File > Options
2. Select Advanced on the left
3. Scroll down to the “When calculating this workbook” section
4. Choose the date format you prefer from the “Use this date system” dropdown
5. Click OK

Now when you apply date formatting, it will use your preferred format by default.

Using Custom Date Formats

In addition to Excel’s built-in date formats, you can create your own custom date formats:

1. Select the cell(s) you want to format
2. Press Ctrl+1 to open the Format Cells dialog
3. On the Number tab, choose Custom from the Category list
4. In the Type box, create your custom format using the following codes:
   • d for day number
   • dd for two-digit day
   • ddd for three-letter day abbreviation
   • dddd for full day name
   • m for month number
   • mm for two-digit month
   • mmm for three-letter month abbreviation
   • mmmm for full month name
   • yy for two-digit year
   • yyyy for four-digit year
5. Click OK to apply your custom format

For example, to display dates as “Mon, Jun 15, 2023” you would use the custom format ddd, mmm d, yyyy.

Custom formats give you complete control over how Excel displays your dates.

Calculating Elapsed Days

Finally, since Excel stores dates as sequential numbers, you can calculate the number of days between two dates by simply subtracting.

For example, if A2 contains 6/1/2023 and B2 contains 6/15/2023, the formula =B2-A2 will return 14 since there are 14 days between June 1 and June 15.

This works even if the cells contain date/time values, since subtracting causes Excel to discard the time portion.

Accounting for Leap Years and Date Systems

When subtracting dates to find the number of days between them, Excel automatically accounts for leap years. It knows that there are 366 days in years divisible by 4 (except centuries not divisible by 400).

However, it’s important to keep in mind that Excel’s date system starts on January 1, 1900 and incorrectly assumes 1900 was a leap year. So date calculations before March 1, 1900 may be off by 1 day.

If you’re working with historical dates and need perfect accuracy, you can switch to the 1904 date system:

1. Go to File > Options > Advanced
2. Scroll down to “When calculating this workbook”
3. Check the “Use 1904 date system” box
4. Click OK

The 1904 system starts on January 1, 1904 and does not have the 1900 leap year bug. However, it is not the default because it can cause compatibility issues with other programs.

For most modern date calculations, the 1900 system works just fine. But it’s good to be aware of this quirk, especially if you’re dealing with old dates.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.