How to Convert Timestamp to Date in Excel (7 Easy Ways)

A timestamp is a sequence of characters used to specify the date and time of a day. Of course, any specific time or event can be expressed in different forms of timestamps. Among these, the most frequently used timestamps are Unix-based timestamps (e.g. 1256953732) and UTC-based timestamps.  (e.g. 2005-10-30 10:45 AM). Today we will learn how to convert each kind of timestamp to date in Excel with suitable examples and proper illustrations.


Download the Practice Workbook

We have prepared a workbook so that you can practice. You can download it from the link below.


7 Easy Ways to Convert Timestamp to Date in Excel

Assume that we have two types of timestamp data as follows:

First, we have Unix-based Timestamp data:Unix-based Timestamp data

And finally, we have UTC-based Timestamp data:

UTC-based Timestamp Data

Now, we will see how we can convert those data to date format. In the first method, we will convert Unix-based Time data to Date. In the next six ways, we will convert UTC-based Timestamp to Date. So, let’s start.


1. Convert Unix Timestamp to Date in Excel

We will follow the steps below to convert the UNIX timestamp to date. Let’s see.

Step 1:

  • Select Cell C5.
  • Enter the following formula:
=B5/86400+ DATE(1970,1,1)
  • Press Enter. The result will be shown in number format.

Convert Unix Timestamp to Date

Step 2:

  • Drag Autofill up to Cell C9 to convert all the timestamps to dates.

Convert Unix Timestamp to Date in Excel

Step 3:

  • Right Click on the selected cells.
  • Select Format Cells from the Context Menu.

A Format Cells dialog box will pop up.

  • Select Date in the Category list Under the Number tab and select the Date Type in the right section. Click OK.

Finally, we can see the date without time.

Related Content: Convert Serial Number to Date in Excel (7 Easy Ways)


2. Convert UTC Timestamp to Date by Hiding the Time

We can convert these to dates easily by hiding the time data. For this, we have to follow these steps:

Step 1:

  • Select Cell C5.
  • Enter the following formula:

=B5

  • Press Enter.

Convert UTC Timestamp to Date by Hiding the Time

Step 2:

  • Drag Autofill up to Cell C9 to convert all the timestamps to date.

Step 3:

  • Right-click on the selected cells.
  • Select Format Cells from the Context Menu.

A Format Cells dialog will pop up.

  • Select Date in the Category list under the Number tab and select the Date Type in the right section.
  • Click OK.

At last, we get the following results.

Convert UTC Timestamp to Date by Hiding the Time

Related Content: Convert Text to Date and Time in Excel (5 Methods)


3. Change Timestamp to Date by Removing Time Using the Find and Replace Tool

We can remove time from the date by using Find and Replace. Let’s see the following steps.

Step 1:

  • Select the range of cells B5:B9.
  • Copy the data and Paste it into C5:C9.

Convert Timestamp to Date by Removing Time  Using the Find and Replace Tool

Step 2:

  • Under the Home tab > Editing group, select Find and Replace > select Replace.

Convert Timestamp to Date by Removing Time  Using the Find and Replace Tool

A pop-up window will show up.

  • In Find what box, type a space followed by an asterisk(*) and leave the Replace with box empty.
  • Click Replace All.

A small pop-up will show “All done. We made 5 replacements.

  • Click OK

  • Click on Close to close the Find and Replace window.

Now, we have got the following results. We have to remove time data from this.

Step 3:

  • Right-click on the selected cells.
  • Select Format Cells from the Context Menu.

A Format Cells dialog will pop up.

  • Under the Number tab, select Date in the Category list and select the Date type in the right section. Click OK.

Now, we have got the desired results.

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


4. Apply Text to Columns Wizard to Get Date from Timestamp in Excel

In this method, we are going to use the Text to Columns wizard to remove time from date.

Step 1:

  • Copy the cells from B5:B9 to C5:C9.

Text to Columns Wizard to Get Date from Timestamp in Excel

Step 2:

  • Select the range of cells C5:C9.
  • From the ribbon section, go to Data > Data Tools > Text to Columns.

Text to Columns Wizard to Get Date from Timestamp in Excel

  • A Convert Text to Columns Wizard – Step 1 of 3 window opens up. Select Delimited. Now Click on Next.

  • On Convert Text to Columns Wizard – Step 2 of 3, select Space. Now Click on Next.

  • On Convert Text to Columns Wizard – Step 3 of 3, select General for the first column.

  • For both the second and third columns, select the Do not import column(skip) Click on Finish.

By following the steps, we get the result as follows:

Step 3:

  • Right Click on the selected cells.
  • Select Format Cells from the Context Menu.

A Format Cells dialog box will pop up.

  • Under the Number tab, select Date in the Category list and select the Date Type in the right section. Click OK.

Now, we have got our desired result.

Related Content: How to Convert Text to Date in Excel (10 ways)


5. Modify Timestamp Using Excel INT Function

Using the INT function is an easy way to remove the time data. Let’s see the following steps.

Step 1:

  • In Cell C5, insert the following formula:
=INT(B5)
Convert Timestamp to Date Using Excel INT Function

Step 2:

  • Fill the rest of the cells using the Autofill.

Convert Timestamp to Date Using Excel INT Function

Step 3:

  • Right-click on the selected cells.
  • Select Format Cells from the Context menu.

A Format Cells dialog will pop up.

  • Under the Number tab, select Date in the Category list and select the Date Type in the right section. Click OK.

We have got our desired result.


6. Alter Timestamp to Date Using DATE Function

We can use the DATE function when we want to take three separate values and combine them to form a date.

Step 1:

  • Type the following formula in Cell C5:

=DATE(YEAR(B5),MONTH(B5),DAY(B5))

Convert Timestamp to Date Using DATE Function

Step 2:

  • Fill the rest of the cells using the Autofill feature to get the complete result.

Convert Timestamp to Date Using DATE Function


7. Convert Timestamp to Date using DATEVALUE and TEXT Functions

In order to use the DATEVALUE function, a date must be saved in TEXT format. That’s why we’ll combine the DATEVALUE and TEXT functions to remove time from a date in Excel.

Step 1:

  • Select Cell C5. Type the following formula:

=DATEVALUE(TEXT(B5,"MM/DD/YYYY"))

Convert Timestamp to Date using DATEVALUE and TEXT Functions

Step 2:

Fill the rest of the cells using the Autofill feature to get the complete result.Convert Timestamp to Date using DATEVALUE and TEXT Functions Note:
The TEXT function converts the value into the text format. On the other hand, the DATEVALUE function just converts the text formatted string to the date value.


Conclusion

In this article, we described how to convert timestamps to date in Excel in seven Easy ways. For this purpose, we have used the INT function, DATE function, DATEVALUE function, Text to Column wizard and Find and Replace Tool for performing this operation. Therefore, I hope this article will solve your problems. Please have a look at our website ExcelDemy.com and let us know if you face any problems in the comment box. There is a practice workbook added. Go ahead and give it a try.


Related Articles

Robiul Hossain

Robiul Hossain

Hello, I am Robiul. I’ve completed my BSc in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology (BUET). I have a keen interest in Programming and Data science. I really love to solve problems that help lots of people in their day to day life. Currently, I am working with a highly skilled and motivated Technical Writing team of ExcelDemy. We are committed to bringing you the best possible results of your Microsoft Excel problems. Stay with us and enjoy the best Excel experience.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo