As you know Excel always has something for someone. When it comes to presenting data in a different format, you can choose as many formats as you need. With a wide range of formats, Excel lets you even edit and modify these formats according to your choice.
Do you know how to remove timestamps from date in Excel? It is a kind of format that people often urge to remove time from dates or timestamps because they only need the dates. In the below-given image, you can find timestamps are present in column A, whereas in Column B you will see only the date value.
Remove Timestamps by Changing Number Format from Date
Here comes our first approach in which you will learn to remove timestamps by changing the number format from date. Let’s follow the steps given below:
- First, a column will be added labeled as Date Without Time to present the outcome without a timestamp.
- Copy the dates from column B to column C.
- Press CTRL + 1.
- The Format Cells dialog box will pop up.
- Open the Date menu from the Number box.
- Now, change the date format and remember that the selected format will have dates only.
- Press OK.
You will notice the dates are present whereas the times are vanished.
That’s it!
How to Remove Timestamp from Date in Excel with Formulas?
In this section, you will learn how formulas in Excel can help you in removing timestamps.
INT Formula
Suppose you need to remove the time part from all the cells given in your dataset and these cells have the date part only.
Here is the INT formula:
=INT(A2)
Using the above formula, you will notice the cell values are taken as the input and give the numeric part while removing the decimal part. As you know, Excel stores date as integer values and time as fractions or decimals, that’s why when you remove the decimal part, only the date part will be left.
Have you noticed that the time part is still appearing? Why is it so?
It is because of the cell format that forces it to show both the date and time. To disappear the time part, you will have to change the cell format and for this, you need to follow a few more steps:
- Open the Home tab.
- Click on the drop-down menu from the Number group.
- You may choose a short or long date format depending on what you need.
That’s it!
The time part has been successfully removed.
TEXT Function
With the TEXT Function, you can simply edit the presentation of numbers based on your desired format codes. Usually, it is helpful only when you need to join numbers with text and symbols. It helps in converting the numbers into text and sometimes you may find it tricky to reference in later analysis.
Syntax of the TEXT function:
TEXT(value, format_text)
Arguments
Let’s have a brief explanation of the arguments used in this formula:
Value – It is a numeric value that converts into text.
Format_text – It lets you add the desired format in which you need the data to appear once you have applied the function.
Let’s follow the steps given below to learn how the TEXT function can help in removing timestamps from date in Excel:
- Go to cell C5.
- Type the TEXT and choose the format as “mm/dd/yyyy”.
The formula will be as:
=TEXT(B5, “mm/dd/yyyy”)
- Press ENTER key.
- Drag the Fill Handle down to the last cell that has data.
That’s it!
DATE Function
Another formula used for the removal of timestamps from dates in Excel is the DATE function. The DATE Function is used when you have three separate values that merged later on to make a date. You will get a serial number in order that shows a certain date.
Syntax of DATE Formula
DATE(year, month, day)
Arguments
Year: In this tear argument, you can add up to 4 digits.
Month: In this section, you can add the month of the year from 1 to 12 or January to December.
Day: In this argument, you can add the day of the month from 1 to 31.
Let’s follow the steps:
- Go to cell C5.
- Enter the DATE formula:
=DATE(YEAR(B5), MONTH(B5), DAY(B5))
- Press ENTER key.
- Drag the Fill Handle down to cell C10.
Summary:
In this post, you have learned how to remove timestamps from dates in Excel by using multiple methods. These methods include the use of different formulas such as INT, DATE, and TEXT. Besides, you have also learned another method in which you change the number format from Date.
You must try all the above methods at least once. It will surely help you in managing data smoothly. Keep exploring Excel!
