Convert TimeStamp in Microsoft Excel

 

To remove the time portion in excel date time stamp in a cell, you have to change the formatting of the cell in Excel.

 

Let’s take an example and understand how we can change the excel timestamp format

For Example: – We have date including time in cell A1. To remove the time from the cell follow the below mentioned steps:-

image 1

 

    • Select the cell A1.
    • Press the key “CTRL+1” on the keyboard.
    • The “Format Cells” dialog box will appear.
    • In the “Number” tab, you can see that the formatting is selected as “dd-mm-yy h:mm”.

img2

 

  • To remove the time on the date time stamp change the formatting to custom.
  • Type in the “Type” box “dd/mm/yyyy”.

img3

 

  • Click on ok.

img4

 

You can see the date is showing in cell A1 without time but in the formula bar it is still reflecting the time. To remove the time permanently, we can use the “INT” formula.

 

INT: - This function is used to round a number down to the nearest integer.

Syntax of “INT” function: =INT (number)

Example:Cell A1 contains the number 456.25
=INT (A1), function will return 456

img5

 

To remove the time on date time stamp follow the below mentioned steps:-

  • Select the cell B1 and write the formula.
  • =INT(A1) and press enter on your keyboard.
  • The function will return the date in cell B1.

img6

 

  • In the formula bar the formula is displayed instead of the date. To convert the formula into values use the “Paste Special” option.
  • Select the cell B1, press the key “Ctrl+C” on your keyboard.
  • Right click of the mouse,select the “Paste Special” option from the popup.
  • The “Paste Special” dialog box will appear, select “Values” and click on ok.

img7

 

img8

 

To conclude, you can see in the above snapshot that the date format has been converted to show only the date without the time.

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 

 



8 thoughts on “Convert TimeStamp in Microsoft Excel

  1. If you have an entire column of dates from which you want to strip the time, here is an easy way:

    1. Select the column.
    2. Choose Format Cells.
    3. Choose number and set decimals to 0. Click Ok. All of the dates will change to a numeric value. Don’t panic.
    4. Choose Format Cells again.
    5. Choose date and mm/dd/yyyy. Click Ok and your dates are back, but without times.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>