# 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.

## 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:

And, we have UTC-based Timestamp data:

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)`

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

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

• After that, right-click on the selected cells you will see the Context Menu.
• Here, select Format Cells from the 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.

Finally, we can see the date without time.

### 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`

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

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

#### 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.

• 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.

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

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

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

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

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

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

#### 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)`

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

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

#### 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))`

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

#### 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"))`

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

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.

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.

## What is ExcelDemy?

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

Advanced Excel Exercises with Solutions PDF