Alok Paul
Working with Microsoft Excel is great fun. We usually work to process data and get information using MS Excel. When we collect data, we use date and time with the corresponding data. In this article, we will discuss how to remove timestamps from date in MS Excel with suitable examples and proper illustrations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Methods to Remove Timestamp From Date in Excel
We will discuss 4 methods to remove timestamps from date in Excel. We have taken a sample time with the date and will remove the timestamps from that.
1. Discard Timestamps by Changing Number Format from Date
Step 1:
- We add a column named Date Without Time to show the result without a timestamp.
Step 2:
- Now, copy the dates from column B to column C.
Step 3:
- Now, press Ctrl+1 .
- A new dialog box named Format Cells will appear.
- From Number go to Date in the box.
Step 4:
- Change the date format. The chosen format will contain only dates.
- Then press OK.
Step 5:
- We see that times are removed and only dates are showing.
In this way, we can easily remove the timestamp.
2. Remove Timestamps Using Excel Formulas
We will apply the formulas to remove the timestamp.
2.1 Use the INT Function
The INT function rounds down a number to its nearest integer number.
Syntax:
INT(number)
Argument:
number – The real number we want to round down to an integer.
We will use the INT Function here.
Step 1:
- Go to Cell C5.
- Write the INT function. The formula will be:
=INT(B5)
Step 2:
- Now, press Enter.
Step 3:
- Drag the Fill Handle icon to the last.
Here, we see that dates are showing with 12:00 AM, as this function round down 00:00 or 12:00 AM is showing. Now, we will remove that also.
Step 4:
- Then, go to the Home tab.
- Select Number from the commands.
- Finally, select Short Date.
Step 5:
- Now, all timestamps are removed from the date.
2.2 Use the TEXT Function:
The TEXT function allows us to change the way a number appears by applying the desired formatting to it with format codes. It is very useful in situations when we want to combine numbers with text and symbols. This will convert numbers to text, which may make it difficult to reference in later calculations.
Syntax:
TEXT(value, format_text)
Arguments:
value – A numeric value that we will convert into text.
format_text – It is the desired format that we want to appear after applying the function.
We will use the TEXT function in this section.
Step 1:
- Go to Cell C5.
- Write the TEXT Select format as “mm/dd/yyyy”. So, the formula will be:
=TEXT(B5,"mm/dd/yyyy")
Step 2:
- Then, press Enter.
Step 3:
- Pull the Fill Handle icon to the last cell containing data.
Here, we see only the date without a timestamp.
Read More: How to Convert Timestamp to Date in Excel (7 Easy Ways)
2.3 Use the DATE Function
The DATE function is used when we want to take three separate values and combine them to form a date. This will return the sequential serial number that represents a particular date.
Syntax:
DATE(year,month,day)
Arguments:
year – This tear argument may contain 1 to 4 digits.
month – It is an integer representing the month of the year from 1 to 12 (January to December).
day- This integer represents the day of the month from 1 to 31.
Step 1:
- Go to cell C5.
- Write the DATE The formula will be:
=DATE(YEAR(B5),MONTH(B5),DAY(B5))- Now, press the Enter key.
Step 2:
- Pull the Fill Handle icon to the Cell C10.
Similar Readings
- How to Remove Numbers from a Cell in Excel (7 Effective Ways)
- Remove Strikethrough in Excel (3 Ways)
- How to Remove Formulas in Excel: 7 Easy Ways
- Insert Timestamp in Excel When Cell Changes (2 Effective Ways)
- How to Automatically Insert Timestamp Data Entries in Excel (5 Methods)
3. Applying Text to Column Wizard in Excel
We will remove the timestamp from the date by applying Text to Column.
Step 1:
- First, select the dates in column C.
- Go to the Data tab.
- Select Data Tools from the commands.
- Select to Text to Columns.
Step 2:
- Select the Delimited and press Next.
Step 3:
- Now, select Space and then Next.
Step 4:
- Now, select the last two columns of the Data Preview option.
- Select Do not import column to skip them.
Step 5:
- Finally, press Finish.
Here, we see that the 12:00 AM timestamp is included.
Step 6:
- To remove this we select Short Date, shown in the below image.
Step 7:
- Finally, we get the date without a timestamp.
4. Use of Excel VBA Macros to Remove Timestamps
We will use VBA & Macro code to remove timestamps.
Step 1:
- Here, we will remove the timestamp from Column C.
Step 2:
- Go to the Developer tab.
- Select Macros from the command.
- Put the Remove_Timestamp on the Macro_name section.
- Then click Create.
Step 3:
- Write the below code on the VBA command module.
Sub Remove_Timestamp()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Date Range"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
Rng.Value = VBA.Int(Rng.Value)
Next
WorkRng.NumberFormat = "mm/dd/yyyy"
End Sub
Step 4:
- Press F5 to run the code.
- Select the range in the Dialog Box.
Step 5:
- Then, press OK.
Conclusion
In this article, we described how to remove timestamps from date in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
