Removing time from a date in Excel can be invaluable when performing calculations that only require the date portion of a date/time value. By using a few simple functions of Excel, an individual can isolate the date from the time and remove the time portion of the date as needed. This article will give clear instructions on how to easily take the time component out of a date in Excel.

Method 1: Using the Format Cells Option

• Step 1: Select the range of cells containing the dates with the time portion.
• Step 2: Select the cells you wish to format and open the Home tab. Click “Format Cells” from the ribbon to open the dialog box.
• Step 3: In the Format Cells dialog box, select the “Number” tab.
• Step 4: From the Category list, choose “Date” or “Custom.”
• Step 5: In the Type field, select the desired date format without the time. A different date format option is available; select either “mm/dd/yyyy” or “dd/mm/yyyy”.
• Step 6: To accept the changes, press the “OK” button. The time portion will be removed, and the dates will be displayed in the selected format.

Using the Format Cells option is a straightforward way to remove the time from dates in Excel. This approach gives you the freedom to tailor the date formatting to suit your needs.

By selecting the range of cells containing the dates with the time portion, you can right-click on the selection to access the context menu. From the context menu, choose “Format Cells” to open the Format Cells dialog box.

In the Format Cells dialog box, navigate to the “Number” tab, where you will find various categories. Decide between “Date” and “Custom” based on your preference.

If you decide on a “Date” format, you have the opportunity to select from a selection of pre-defined date formats. Opting for the ‘Custom’ function gives you the option to select your own personalized format for date display.

In the Type field, choose the desired date format without the time component. If you prefer the dates to be displayed as “mm/dd/yyyy” or “dd/mm/yyyy,” simply choose the correct format.

Once the preferred format is chosen, press the “OK” button to confirm the adjustments. Excel will remove the time portion from the dates and display them in the selected format. The dates will now be shown without the time, allowing you to work with cleaner and more concise data.

Using the Format Cells option provides a quick and efficient way to remove the time from dates in Excel. This method is particularly useful when you want to visually present the dates or perform calculations that solely rely on the date component.

Method 2: Using the Text Function

• Step 1: In an empty column, next to the column containing the dates with time, enter the following formula: =TEXT(A2, “mm/dd/yyyy”) – assuming your date is in cell A2.
• Step 2: Drag the formula down to apply it to the rest of the cells in the column.
• Step 3: The formula converts the date with time into a text string with the desired date format. You can change the format in the formula by modifying the “mm/dd/yyyy” part to suit your preference.
• Step 4: Once you have converted the dates to text, you can copy the column and paste it as values (right-click and choose “Paste Special” -> “Values”) to remove the formula and retain only the date values.

Using the Text function in Excel allows you to convert dates with time into text strings with a specific date format. This method ensures that the time component is removed, leaving only the date information.

Begin by selecting an empty column adjacent to the column containing the dates with time. In the first cell of the new column, enter the following formula: =TEXT(A2,”mm/dd/yyyy”), assuming the date with time is located in cell A2. Adjust the cell reference accordingly to match the location of your date.

Drag the formula down to apply it to the remaining cells in the column. As you do so, the formula will convert each date with time into a text string using the specified format. In this example, the format “mm/dd/yyyy” is used, but you can modify it to suit your preference. For instance, you can use “dd/mm/yyyy” or any other date format available in Excel.

After converting the dates to text, you may choose to remove the formula and keep only the date values. To do this, select the column with the converted dates, right-click, and choose “Copy” (or use the keyboard shortcut Ctrl+C).

Next, right-click on the same column and choose “Paste Special” from the context menu. In the Paste Special dialog box, select “Values” and click on the “OK” button. This action pastes the values of the dates without the formula, effectively removing the time component.

Using the Text function provides flexibility in formatting dates with time and allows you to convert them into text strings with the desired date format. By subsequently pasting the values, you can retain the date information while discarding the formula, simplifying further analysis and manipulation of the data.

Method 3: Using the INT Function

• Step 1: In an empty column, next to the column containing the dates with time, enter the following formula: =INT(A2) – assuming your date is in cell A2.
• Step 2: Drag the formula down to apply it to the rest of the cells in the column.
• Step 3: The INT function converts the date with time into an integer value, removing the decimal portion, which represents the time.
• Step 4: Similar to Method 2, you can copy the column and paste it as values to retain only the date values.

Using the INT function in Excel allows you to remove the time portion from dates by converting them into integer values. This method effectively discards the decimal portion, which represents the time, leaving only the date component.

To begin, select an empty column adjacent to the column containing the dates with time. In the first cell of the new column, enter the following formula: =INT(A2), assuming the date with time is located in cell A2. Adjust the cell reference as needed to match the location of your date.

Drag the formula down to apply it to the remaining cells in the column. As you do so, the INT function will convert each date with time into an integer value. This conversion removes the decimal portion, which represents the time.

After converting the dates to integer values, you may choose to remove the formula and keep only the date values. To do this, select the column with the converted dates, right-click, and choose “Copy” (or use the keyboard shortcut Ctrl+C).

Next, right-click on the same column and choose “Paste Special” from the context menu. In the Paste Special dialog box, select “Values” and click on the “OK” button. This action pastes the values of the dates without the formula, ensuring that only the date component remains.

Using the INT function allows for quick and efficient removal of the time portion from dates. By converting the dates into integer values, you can easily manipulate and analyze the data without considering the time component. Remember to paste the values to retain the date values while removing the formula, facilitating further calculations and analysis in Excel.

Conclusion

Removing the time portion from a date in Excel is a simple process that can be achieved using various methods. Whether you prefer using the Format Cells option, the Text function, or the INT function, Excel offers multiple approaches to suit your requirements. By following the steps outlined in this article, you can effectively eliminate the time information from your dates, ensuring cleaner data for your analysis and presentation needs.

Read more.