Robiul Hossain
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.
Download the Practice Workbook
We have prepared a workbook so that you can practice. You can download it from the link below.
2 Suitable Cases to Convert Timestamp to Date in Excel
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 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 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.
Related Content: Convert Serial Number to Date in Excel (7 Easy Ways)
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 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 Method 1 and you will get the following results.
Related Content: Convert Text to Date and Time in Excel (5 Methods)
2.2. Applying Text to Columns Wizard
Excel has a very special feature for performing multiple type 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 Method 1 and you will get the following results.
Related Content: How to Convert Text to Date in Excel (10 ways)
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.
Read More: How to Remove Timestamps from Date in Excel (4 Easy Ways)
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.
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 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 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.
