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