Excel means that you can show knowledge in many various codecs. And there are approach too many to select from on the subject of dates and time knowledge in Excel.
And since there are such a lot of codecs, there are additionally methods to edit and modify these.
One frequent factor many individuals must do usually is to take away time from dates (i.e., take away the time from the timestamp so that you just solely have the date).
Beneath is an instance the place column A has the timestamps (with date and time each) and Column B solely has the date worth.
On this tutorial, I’ll present you a few methods to take away time from the date in Excel.
One factor it’s good to know earlier than I soar into the strategies is that date and time are saved as numbers in Excel. Whereas you may even see a date January 01, 2020, in actuality, itÂ’s the quantity 43831 on the backend. Equally, time can be saved as numbers. For instance, January 01, 2010 three:00 PM is the quantity 43831.63 within the backend (the place the integer half represents the date and decimal half represents the time).
Table of Contents
Change the Format to Disguise the Time from Timestamp
If all you need is to indicate the date and never the date and time each, you’ll be able to simply do this by altering the format of the cells.
Because of this you don’t truly take away the time from the timestamp, however solely hiding the time half.
Beneath is an instance, the place I’ve the identical date worth in each the cells, however since these are formatted otherwise, you see the time in a single and never within the different.
As you’ll be able to see within the picture above, the formulation bar reveals the date and time each whereas the cell solely reveals the date
Beneath are the steps to take away/cover the time from the date by altering the cell format:
Choose the cell wherein you will have the timestamp from which you need to take away the timeHold the Management key after which press the 1 key. It will open the Format Cells dialog boxIn the Format Cells dialog field, choose the Quantity tabWithin the left pane, click on on DateWithin the choices on the correct, choose the format wherein you need the date to be displayedClick on OK
The above steps would immediately cover the time half from the timestamp and you’ll solely see the date.
Keep in mind that this technique solely hides the time half and doesnÂ’t take away it. Because of this should you use these cells in any calculations, it might embody the date and effectively as time worth.
There are two good issues about this technique:
You donÂ’t want a separate column to get the outcome. Simply choose the cells with the info and alter the format for these cells itself.It doesnÂ’t alter the unique worth – solely modifications the best way itÂ’s being displayed. So in case it’s good to use the unique knowledge, you continue to have it intact.
In case you need to take away the time portion from the timestamp utterly, higher to make use of the formulation technique (lined subsequent) or the Discover and Exchange technique or the Textual content to Columns (lined after that)
Take away Time from Date Utilizing Formulation
There are a few straightforward formulation that you should utilize to take away the time half from the timestamp and solely get the date.
On this part, I’ll present you the way to do that utilizing three formulation – INT, DATEVALUE, and TEXT.
Utilizing the INT Formulation
Suppose you will have the dataset as proven beneath, and also you need to take away the time from all these cells and solely have the date half.
Beneath is the INT formulation that may do that:
=INT(A2)
The above formulation takes the cell worth because the enter and returns solely the integer half and removes the decimal half. Since dates are saved as integer values and time is saved as fraction/decimal in Excel, whenever you take away the decimal half, you might be solely left with the date half.
However you continue to see the time half (12:00 AM) within the outcomeÂ… why is that?
ThatÂ’s due to the format of the cell, which is forcing it to indicate the date in addition to the time. And for the reason that time worth is zero, it reveals 12:00 AM for all of the cells.
So now to not present the time half, you’ll have to change the format to solely present the date and never the time.
Listed here are the steps to solely present the date and never the time in Excel:
Click on the Residence tabWithin the Quantity group, click on on the drop-downChoose Quick date (or Lengthy Date) – based mostly on the way you need the date to be displayed.
ThatÂ’s it!
You’ve gotten efficiently eliminated the time from the date.
Keep in mind that with this formulation, you will have truly modified the worth by eliminating the decimal half. That is totally different than the tactic lined earlier than this (the place we merely modified the format to cover the time portion).
Now, letÂ’s see one other formulation to do that.
Utilizing the DATE VALUE formulation
Whereas the INT formulation extracts the integer half and discards the decimal half (which is the time portion), the DATEVALUE operate has one job – to provide the worth of the date from a timestamp.
So, if I give the enter worth as 29-02-2020 02:36:43, it can give me solely the worth for the date and never the time.
Beneath is the syntax if the DATEVALUE formulation:
=DATEVALUE(date_text)
DATEVALUE solely takes one single argument, but it surely must be the date within the textual content format. Because of this if I instantly give the date or the cell that incorporates the date because the enter, that is going to return an error.
I want to verify the date is in textual content format. So I must mix the DATEVALUE operate with the TEXT operate to get the outcome.
Beneath is the formulation that can take away the time from the date:
=DATEVALUE(TEXT(A2,”dd-mm-yyy”))
The above formulation makes use of the TEXT operate to take the date and provides it within the specified textual content format. That is then utilized by the DATEVALUE operate to return the one the date a part of the timestamp.
You will notice the outcome as a quantity – which is the numeric worth for the date. You possibly can manually change the format of the outcome to a brief/lengthy date, or you should utilize the beneath formulation:
Within the above formulation, I’ve wrapped the DATEVALUE outcome within the TEXT formulation and specified the format as “dd-mm-yyy”. This is able to give the outcome as a textual content worth, however you donÂ’t have to fret in regards to the format.
Take away the Time From Date utilizing Discover and Exchange
In case your timestamp knowledge has a format as proven beneath, you should utilize the discover and exchange performance to shortly eliminate the time portion.
Suppose you will have the dataset as proven beneath and also you need to take away the time half and solely have the date.
Beneath are the steps to take away time from date utilizing Discover and Exchange:
Choose the cells from which you need to take away the timeClick the Residence tabWithin the Enhancing group, click on on Discover and Choose choiceClick on on the Exchange choice. It will open the ‘Discover and Exchange’ dialog fieldWithin the Discover what: discipline, enter * (an area character adopted by the asterisk image)Go away the Exchange with: discipline cleanClick on on Exchange All
The above steps would immediately take away the time half from the cell and you can be left solely with the date.
However you should still see the outcome as proven beneath the place the date additionally has the time portion (though on a regular basis worth is 12:00 AM).
This occurs as we have now gotten rid of the time worth from the general date, however we nonetheless havenÂ’t modified the format to solely present the date and never the time.
Word that this works solely when you will have the info in a format the place the date is adopted by an area character after which the time. In case you will have main areas, this technique is not going to work. You’ll first must eliminate main areas after which use this technique.
Beneath are the steps to solely get the date and never present the time half:
Click on the Residence tabIn the Quantity group, click on on the drop-downSelect Quick date (or Lengthy Date) – based mostly on the way you need the date to be displayed.
One advantage of utilizing Discover and Exchange over formulation is that you just donÂ’t want an additional column to get the outcome. You possibly can have the ends in the identical cells the place you had the unique knowledge. Simply be sure you create a backup copy in case it’s possible you’ll want the unique knowledge sooner or later.
Take away the Time From Date utilizing Textual content to Columns
Textual content to Columns is generally used to separate textual content in a cell.
And on this instance, I’ll use it to separate the time stamp into date and time. As soon as carried out, I’ll solely be left with the date half, which I can then format to indicate the date nevertheless I would like.
Suppose you will have a dataset as proven beneath:
Beneath are the steps to make use of Textual content to Columns to take away the time portion from the date:
Choose the cells from which you need to take away the timeClick the Information tabWithin the Information Instruments group, click on on ‘Textual content to ColumnsÂ’ choice Within the Textual content to Column Wizard, do the next:Step 1 of three: Choose the ‘DelimitedÂ’ choiceStep 2 of three: Choose House because the delimiterStep three of three: Within the Information preview part, click on on the column that incorporates the time values after which choose the ‘Don’t import column (skip)Â’ choice. Maintain the vacation spot cell the identical the place you will have the infoClick on on End
The above steps would take away the time portion from the cells and you’ll solely be left with the date.
Nonetheless, you’ll nonetheless see the time with every date (the place the time can be 12:00 AM or one thing comparable). It’s because the format of the cells nonetheless forces the time to be proven,
Beneath are the steps to alter the format so it solely reveals the date:
Click on the Residence tabIn the Quantity group, click on on the drop-downSelect Quick date (or Lengthy Date) – based mostly on the way you need the date to be displayed.
With Textual content to Columns, you too can get the ensuing knowledge in separate cells (and hold the unique knowledge as is). To do that, it’s good to specify the vacation spot cell in Step three of Textual content to Columns wizard.
These are some easy and fast methods to take away time from dates in Excel. The tactic you select will rely on whether or not you need to hold the unique knowledge or not. If you use formulation, it provides you with the lead to a separate column and whenever you change the format or use Discover and Exchange or Textual content to Columns, it provides you with the lead to the identical cells itself.
