How to Remove Timestamps from Date in Excel (4 Easy Ways)

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.

Data set to Remove Timestamps from Date in Excel


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.

Discard Timestamps by Changing Number Format from Date

Step 4:

  • Change the date format. The chosen format will contain only dates.
  • Then press OK.

Discard Timestamps by Changing Number Format from Date

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)

Remove Timestamps Using Excel Formulas

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.

Remove Timestamps Using Excel Formulas

Step 5:

  • Now, all timestamps are removed from the date.

Remove Timestamps Using Excel Formulas

 


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")

Remove Timestamps Using Excel Formulas

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))

Remove Timestamps Using Excel Formulas

Step 2:

  • Now, press the Enter

Step 3:

  • Pull the Fill Handle icon to the Cell C10.

Remove Timestamps Using Excel Formulas


Similar Readings


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.

Applying Text to Column Wizard in Excel

Step 2:

  • Select the Delimited and press Next.

Applying Text to Column Wizard in Excel

Step 3:

  • Now, select Space and then Next.

Applying Text to Column Wizard in Excel

Step 4:

  • Now, select the last two columns of the Data Preview
  • Select Do not import column to skip them.

Applying Text to Column Wizard in Excel

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
  • Select Macros from the command.
  • Put the Remove_Timestamp on the Macro_name
  • Then click Create.

Use of Excel VBA Macros to Remove Timestamps

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

 

Use of Excel VBA Macros to Remove Timestamps

Step 4:

  • Press F5 to run the code.
  • Select the range in the Dialog Box.

Use of Excel VBA Macros to Remove Timestamps

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.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo