How to remove time from date in excel
When the Excel user uses the date or time formula in Excel, they sometimes get the combined date and time if the timestamp is set to dd-mm-yyyy hh:mm. It is general, but sometimes users need only date, not time. In such a scenario, this chapter will help you out to remove the time from date/timestamp when you only need time.
Timestamp returns the date and time both. So, in this chapter, you will learn how you can get only dates from the timestamp used in Excel. In this, we are dealing with the timestamp that includes date and time.
What to do to get only date?
Sometimes, the Excel users only require date rather than date with time. However, there are various functions in Excel that can be used to get the only date as a resultant value. What if you receive a workbook from someone else that contains the date with time?
In such cases, the users need to remove the time from it explicitly. They can use Excel features or functions to extract the date from the timestamp.
The Excel users can use the formatting to create a timestamp without time if they need dates only. Formatting data is an interesting feature of Excel. You will see how one can use it to format the data in Excel. This can also be used to extract the date from the timestamp.
Ways to remove time from date
We have the following methods to get the date only:
- Set the date format using formatting
- Remove the time from date
- Remove time from date using Excel formula
- Remove time from date using Find and Replace
Set the date format using formatting
For example, if you use the NOW() function of Excel to get the current date from the system, you will get both date and time. You can follow the process we are going to discuss below to remove the time from date by using the Excel formatting feature.
Following are the steps for it:
Step 1: Select the number of cells you want to format that will contain only date input.
Step 2: Inside the Number group, click the General category dropdown list.
Step 3: Click the More Number Formats at the end of the dropdown list that will open a dialogue panel.
Step 4: Choose Custom from the left sidebar and enter dd-mm-yyyy in the Type field that is the format to get only date when you only want date, not time. Then, click OK.
Step 5: Now, enter the date or use the NOW() function to get the date in the formatted cell.
After selecting the dd-mm-yyyy format for date in the previous step, the selected cell will hold date, but not time.
Remove the time from date
Let’s take a scenario – your Excel sheet contains data as combined date and time (dd-mm-yyyy hh:mm) format. You want to extract the date by removing the time from it. Excel users can apply the data formatting feature for this.
Go through with the steps below:
Step 1: Select the cells with date and time data from which you want to remove time and keep only date. Then, right-click on it and choose Format cells.
Step 2: Make sure that you are on the Custom category inside the Number tab. Here, select the dd-mm-yyyy format for date to get only date from the selected date and time data and then click OK.
If you are not on custom, navigate to it.
Step 4: You will now see that the date has been extracted from the selected data and time has been removed.
By using the data formatting feature of Excel, one can easily format the date and time data. The users can also choose another format for the date.
The cell formatting makes changes in original data. It does not keep original data and modified data separate.
Remove time from date using Excel formula
Another way to remove the time from the date stored in an Excel sheet is using the Excel formula. Using the Excel formula, you can extract the date from the data by removing the time and keeping it separate from the original data.
In the end, you will have the original and resultant data. These functions help keep the original data and resultant data in separate cells. We will expand the three formulas that will help the Excel users to remove the time from date.
These functions will help you out to remove the time from date if having. Let’s discuss how these functions work to remove the time.
INT()
INT() is a basic function of Excel that is used to round off a number to its nearest integer. You are wondering that how this function can help in case of timestamps. Learn with an example.
Although it is a lengthy process to work with, but it is still helpful in some way. This function will be used with a single argument that will contain the date along with time.
Tip: First of all, cells of the Excel sheet should be in dd-mm-yyyy hh:mm format. Otherwise, the time will not store with the combined date and time.
Example
Step 1: We have the dates with time in column A. We will use the following formula in another cell to get the extracted data in a separate column.
=INT(A2)
Step 2: Now, hit the Enter key and get the date without time in another cell where you wrote the formula.
You will notice that the time has been removed but it leaves 0 in place, which is resultant as 0:00. This is because of the custom format. So, switch from the custom format to a date format.
Step 3: We have used the same formula for all other given dates by just changing the cell reference for each data. See the result for all.
Change to date format from custom format
Step 4: Select all the resultant cells you got by using the INT() function.
Step 5: Now, in the Home tab, go to the Number section and click on the bar having Custom for the current date format.
Step 6: Choose either Short Date or Long Date. We have chosen Short Date as it is most commonly used.
Step 7: You will now see that the time is now completely removed from the resultant cells (column B) and only dates remain.
You still have the original data too. This is the main benefit of using Excel formula instead of data formatting feature. You do not lose the original data and get the resultant one too.
DATE() function with MONTH, YEAR, and DAY function
The next Excel formula to remove the time from date is DATE() function that can be used with MONTH, YEAR, and DAY function to achieve this target value data.
Basically, the DATE() function returns the date in dd-mmmm-yyyy format. We will make a little bit of change in the formula by adding the YEAR, MONTH, and DAY function instead of year, month, and day. Basically, we will do the nesting of these functions inside the DATE() function to extract the date and ignore the time.
Let’s see the syntax for it –
Here, A2 cell contains the date along with time.
Example
We will use the same dataset of dates with time as used in the above example and then apply the DATE() function on it to remove the time from date.
Step 1: We have the dates with time in column A. We will use the following formula in column to get the extracted data in a separate column.
=DATE(YEAR(A2), MONTH(A2), DAY(A2))
Step 2: Now, hit the Enter key and get the date without time in another cell where you wrote the formula.
You will see that the date has returned without time but has 0 in place of time. The date is displayed as dd-mm-yyyy 0:00 format. The time has been removed.
Step 3: We have used the same formula for all other given dates by just changing the cell reference for each data. See the result for all.
Change to date format from custom format
Step 4: Select all the resultant cells you got by using the INT() function.
Step 5: In the Home tab, go to the Number section and click on the bar having Custom for the current date format.
Step 6: Choose either Short Date or Long Date. We have chosen Short Date as it is most commonly used.
Step 7: You will now see that the time is now completely removed from the resultant cells (column B), and only dates remain.
TEXT()
The TEXT() function is the last function using which the Excel user can remove the time from date. It converts a value to text in a specific number format. This will also be as easiest as other methods were.
You can also use this function to remove the time from date by converting the timestamp into a date of your choice of date format. You will see how this function will be used in Excel.
Let’s see the syntax for it –
Here, A2 cell contains the date along with time.
The TEXT() function picks up the date from the timestamp and returns it according to the defined format in the second argument of the function.
Example
We are going to use the same dates data with time that we have used in the above example. You will notice at the end the result will be the same without time after applying the TEXT() function on it.
Step 1: Apply the following text formula to extract the date from timestamp and remove the time from it.
Step 2: Hit the Enter key and get the result extracted from the timestamp stored in the A2 cell.
You see that the TEXT() function returned the result as text value as it is placed at the left in the cell.
Step 3: Before proceeding further, apply the same formula on the other data by just changing the cell reference for each of its corresponding data.
Note: All the values returned by the TEXT format are placed left in the cell. It means that it returned values as text format instead of date format.
Advantage of using TEXT() function
The main advantage of using the TEXT() function is that – the users do not need to change the format as we have done in other functions (INT, DATE). You get the date without time by just using a single process. However, the date returned by this function is in text format instead of date format.
Remove time from date using Find and Replace
In this chapter, we are dealing with the timestamp from which we are removing time from date. You have looked for various methods (features and functions) to remove the time from date.
Find and Replace is another feature of Excel that will enable the users to work for the timestamp along with date formatting. It devalues the time that can be formatted out of the timestamp. Let’s see how this feature will be used.
Following are the steps for it:
Step 1: Select all the dates with time (timestamp) from which you want to remove time.
Step 2: Press the Ctrl+H key to open the Find and Replace dialogue box. Or, In the Home tab, click Find & Select button and then choose Replace.
Step 3: Do the following:
- Type a space and then an * (asterisk) in the Find What field.
- In the Replace With field, do nothing.
- In the end, click Replace All.
Note: This method works fine when the date and time are separated by single whitespace.
All the replacement will be done by removing the time from the date wherever it found a single whitespace between date and time.
Step 4: Click OK to close the popup of replacement made. Five replacements have been done.
Also, close the Find and Replace dialogue box by clicking the Close button.
Step 5: You will notice one thing that the time of the timestamp has changed to 0:00. It means that the time has been replaced with a nil value, i.e., 0.
Now, it’s time to format the date for which we will use the cell formatting feature of Excel to remove the time completely.
Step 6: Select the dates with a nil time value, get from previous steps.
Then, In the Home tab, choose a date format, either Short Date or Long Date. We will choose Short Date.
Step 7: Now, you will see that the time has been completely removed from the date.
Apart from these, there are several more methods offered by Excel using which the users can remove the time from date. They can use them according to their suitability or find them easier.
We have discussed various methods, but there might be more. We have done with it, but you can also do it TEXT to columns feature in Excel. This will help you too. However, the above methods are enough. You will no need for more; you may get confused otherwise.
