How to Convert Timestamp to Date in Excel (2 Suitable Cases)

Get FREE Advanced Excel Exercises with Solutions!

In short, 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). In this article, we will learn how to convert timestamp to date in Excel for both Unix and UTC with suitable examples and proper illustrations.

Before starting the procedure, let’s have a quick look to the features and formulas we are going to use in our methods to convert timestamp to date in Excel.

Overview of the article on how to convert timestamp to date in Excel with formulas and features


How to Convert Timestamp to Date in Excel: 2 Suitable Cases

Let’s say, we have two types of timestamp data as follows:

First, we have Unix-based Timestamp data:

Dataset of Unix timestamp with Unix time and date columns

And, we have UTC-based Timestamp data:

Dataset of UTC timestamp with Date-time and date columns

Now, considering these two cases, we will see how we can convert timestamp to date in Excel. In the first method, we will convert Unix-based Time data to Date. For the next 5 ways, we will convert UTC-based Timestamp to Date. So, let’s start.


1. Convert Unix Timestamp to Date

The way of tracking time with the total of running seconds is known as Unix timestamp. This type of timestamp started on 1 January, 1970 at UTC.

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

Steps:

  • First, select Cell C5.
  • Then, type the following formula with the DATE function in that cell:

=B5/86400+ DATE(1970,1,1)

Apply DATE function and arithmetic formula to convert timestamp to date in Excel

  • Next, press ENTER. The result will be shown in number format.

Drag Fill Handle to Copy formula

  • Now, drag the Fill Handle tool to Autofill up to Cell C9 to convert all the timestamps to dates.

Result of applying formula to convert timestamp to date

  • After that, right-click on the selected cells you will see the Context Menu.
  • Here, select Format Cells from the Menu.

Select Format Cells from context menu

Hence, the Format Cells dialog box will pop up.

Note: You can also perform the same task by pressing CTRL+1.
  • Next, select Date in the Category list Under the Number tab and select the Date Type in the right section. Click OK.

Choosing date format in the Format Cells dialog box in Excel

Finally, we can see the date without time.

Results showing conversion of Unix timestamp to date in Excel

Read More: How to Convert Active Directory Timestamp to Date in Excel


2. Convert UTC Timestamp to Date

We have UTC timestamp in our Dataset 2. Now, we will see several methods to convert the UTC timestamp to date.


2.1. Using Format Cells Option

We can convert these to dates easily by hiding the time data using the Format Cells dialog box like Mehtod 1. You can customize format of cells using this command. For this, we have to follow these steps:

Steps:

  • First of all, select Cell C5.
  • Now, apply the following formula in that cell:

=B5

Enter B5 as Cell Reference

  • Then, press ENTER.
  • After that, copy the formula up to Cell C9 to convert all the timestamps to date.

Result showing the application of formula with fill handle tool

  • Now, open the format cells dialog box and repeat the same steps in Method 1 and you will get the following results.

Results showing conversion of UTC timestamp to date in Excel with format cells option


2.2. Applying Text to Columns Wizard

Excel has a very special feature for performing multiple types of tasks => Text to Columns wizard. In this method, we are going to use this wizard to remove time from date.

Steps

  • First of all, copy the cells from B5:B9 to C5:C9.

Copy and paste data from a range of cells to another

  • Next, select the range of cells C5:C9.
  • Now, from the Ribbon section, go to Data tab > then Data Tools group > select Text to Columns option from the context.

Selecting Text to Columns option in the data tab

  • Here, the Convert Text to Columns Wizard – Step 1 of 3 window opens up.
  • Now, select Delimited and click on Next.

Step 1 of Convert Text to columns wizard choosing delimited option

  • Then, in the Convert Text to Columns Wizard – Step 2 of 3, select Space and click on Next.

Step 2 of Convert Text to Columns wizard selecting space delimiter

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

Step 3 of Convert Text to Columns wizard choosing general data format

  • And, for both the second and third columns, select the Do not import column(skip)
  • Now, click on Finish.

Selecting do not import column and closing text to column wizard

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

Output from Text to columns in Excel

  • Finally, open the format cells dialog box and repeat the same steps in Method 1 and you will get the following results.

Results showing conversion of UTC timestamp to date in Excel with text to columns option 


2.3. Modifying Timestamp to Date Using INT Function

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

Steps

  • First of all, insert the following formula in Cell C5.

=INT(B5)

Apply INT function in Excel

  • Then, fill the rest of the cells using the Autofill.

using fill handle to copy formula to the cells below

  • Now, repeat the same steps of Method 1 and you will get the following results.

Results showing conversion of UTC timestamp to date in Excel with INT function


2.4. Implementing DATE Function to Change Timestamp to Date

We can use the DATE function when we want to take three separate values and combine them with YEAR and MONTH, DAY functions to form a date.

Steps

  • At first, apply the following formula in Cell C5:

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

Applying DATE, YEAR, MONTH, DAY functions in Excel

  • After that, fill the rest of the cells using the Autofill feature to get the complete result.

Results showing conversion of UTC timestamp to date in Excel with DATE, YEAR, MONTH, DAY functions


2.5. Combining DATEVALUE and TEXT Functions to Alter Timestamp to Date

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.

Steps

  • Firstly, select Cell C5 and apply the following formula:

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

Applying DATEVALUE and TEXT functions 

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

Results showing conversion of UTC timestamp to date in Excel with 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.

Download the Practice Workbook

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


Conclusion

In this article, we have described almost all possible ways to convert timestamps to date in Excel considering two suitable cases. For this purpose, we have used the INT, DATE, and DATEVALUE functions, Text to Column wizard and Find and Replace Tool for performing this operation. Therefore, I hope this article will solve your problems and let us know if you face any problems in the comment box.


Related Articles


<< Go Back to Convert to Date | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo