Yousuf Khan
Microsoft Excel is a handy software. We use Excel functions and features for our educational, business, and other daily life purposes. Furthermore, Excel provides some special functions to exclude a date from the date-time format in our workbooks. This article will show you 4 simple methods to Truncate a Date in Excel.
Download Practice Workbook
You can download this workbook to practice yourself.
4 Simple Methods to Truncate Date in Excel
Sometimes, our datasets have date/timestamp formats. As a result, the datasets look congested. However, excluding dates from date-time formats in Excel can be really cumbersome if we consider a large amount of dataset. In this article, I will show you 4 easy methods to truncate dates in Excel. For instance, we take a dataset that represents the client meeting schedule of a paper company.
1. Truncate Date Using INT Function in Excel
In this method, we will use the INT function for removing dates in Excel. First, let’s learn a little bit about this function. The INT function sees the date/timestamp data as integer and decimals/fractions (date as integer & time as decimal/fractions). Afterward, the INT function rounds up a decimal value to the lowest integer fraction and returns an integer value. Here, we extract the date from the date/timestamp and afterward subtract it. Let’s follow these steps to truncate date from a date-time format given in column B.
Steps:
- To begin with, type the formula in D5,
=B5-INT(B5)- If you want to get only decimal values, this formula saves a lot of time.
- Later, hit Enter.
- In this way, we truncate the date and get the remaining time value in cell D5.
- At last, drag down the formula cell up to D9 to AutoFill the other cells.
🔎 How does the formula work?
- Here, INT(B5) takes the date values as integers and time as fractions and returns only the date as integers.
- Later, we subtract it from B5 and get only the decimal portion which shows the time value only.
Read More: How to Truncate Text from Left in Excel (5 Suitable Ways)
2. Apply Custom Formatting to Exclude Date
In this method, we will customize the Format Cells option to exclude date from date-time data. The dataset in column B represents dd:mm:yy hh:mm format. We will customize this into hh:mm AM/PM format to obtain the desired result. This is a Time format and by implementing this we can truncate the dates. To do so, follow the steps.
Steps:
- First, select the range D5:D9 containing date-time data.
- After that, right-click on the selected cells to open the options menu.
- Eventually, it pops up then we click Format Cells.
- Subsequently, the Format Cells dialogue box opens up.
- Here, go to Number > Time and select a time format from the Type options box.
- Finally, tap OK.
- See the picture below to understand better.
- Thus, we get the dates truncated.
Read More: How to Truncate Text in Excel (6 Suitable Methods)
3. Insert Excel TRUNC Function for Removing Date
Firstly, let’s see what the TRUNC function is. The TRUNC function is under the Math and Trigonometry Functions category which truncates the decimal portion of a number and returns only an integer value. The Excel TRUNC function converts a number from a decimal or fractional representation to an integer. In this method, we will implement this function to truncate the date from the date-time value. Let’s go through some procedures to do so.
Steps:
- Firstly, in cell D5, write the following formula,
=B5-TRUNC(B5)- After that, press Enter to get the result.
- After we have our result in cell D5, drag it down to AutoFill the column.
- Hence, we obtain our result.
🔎 How does the formula work?
- In this formula, TRUNC(B5) sees the date-time as integer and decimal values. It breaks them down and truncates the decimal and returns only integer values.
- Secondly, we subtract it from B5 and get only the decimal portion which shows the time value only.
Read More: How to Truncate Text from Right in Excel (5 Easy Methods)
4. Truncate Date Through Excel VBA
In the last method, we will truncate the date through Excel VBA. We can execute a simple VBA code and remove dates from our dataset. Follow the procedures to do so.
Steps:
- In the beginning, go to the Developer tab and tap Visual Basic to open the Visual Basic window.
- Afterward, click Insert then Module to create a module.
- Consequently, a module box pops up.
- Now, in the module box, type the formula,
Sub Truncate_Date()
With Range("D5")
   .Value = TimeValue(.Value)
   .NumberFormat = "hh:mm:ss AM/PM"
End With
End Sub- Next, press Run to execute the code.
- As a result, we remove the date and obtain the desired time format in cell D5.
- Finally, AutoFill the remaining cells by dragging the formula cell down.
Conclusion
In conclusion, we have discussed some easy ways to truncate a date in Excel. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please leave any further queries or recommendations in the comment box below.
