Unix Timestamp conversions in Excel and in Power query are becoming more and more common. With the ability to connect to a vast variety of data sources, the likelihood of you coming across a Unix timestamp is increasing. The first time I came across a UNIX-based time was when I was connecting to a database from Excel and although the field was named Time, I had no idea what all these numbers meant, and I was left totally confused.
Unix time is commonly used in operating systems and programs that timestamp transactions as they happen. The reason Unix time is used is that no matter where you live or the time zone you are in, this Unix timestamp represents a moment that is the same everywhere.
So, what is Unix time? Unix time is the duration in seconds or milliseconds from midnight of the 1st January 1970 in UCT time. It is represented by a 32-bit integer that can be positive or negative. There is however a limitation of Unix time. Unfortunately, there are upper and lower bounds which restricts the actual time span available. Unix time spans from December 13th, 1901, and will end on the 19th of January 2038. This is known as the Y38 Problem.
So now that you have a basic knowledge of Unix time, how can we work with this in Excel or Power Query? Throughout the rest of this article, you are going to learn how to carry out Unix Timestamp conversions in Excel. We will convert back and forth from Unix time to Excel time in both Excel and Power Query.
How to convert Unix time to Excel Time
Let us first explore converting Unix time to Excel Time. First, we will look at the formula when the timestamp is in second and then we will look at the formula when the timestamp is in milliseconds.
When time stamp in Seconds
The formula used to convert Unix timestamp recorded in seconds is:
= (Unix time/86400) +DATE (1970,1,1)
Remember to replace Unix Time with the cell reference that contains the Unix timestamp.
Once you enter this formula into the cell, the formula can be dragged down and copied to other cells in the workbook.
When the timestamp is in Milliseconds
The formula used to convert Unix timestamp recorded in milliseconds is:
= ((Unix time/1000)/86400) +DATE (1970,1,1)
Remember to replace Unix Time with the cell reference that contains the Unix timestamp.
How to convert Excel time to Unix time
With a reversal of the formula, we used to convert Unix time to Excel time, we can covert Excel time to Unix time.
The formula to convert Excel time to Unix time in seconds is.
= ((Excel Time - DATE(1970,1,1)) *86400
Remember to replace Excel Time in the formula with the cell reference that contains the time you wish to convert to Unix time.
The formula to convert Excel time to Unix time in milliseconds is.
= ((Excel Time -DATE (1970,1,1)) *1000) *86400
How to convert Unix time in Power query
Power query is used in both Excel and Power BI to connect to and transform data into a useable format. The more you connect to different data sources the more often you will come across date columns in Unix format. Once you have the data in Power query, a calculated column can be added to the data to carry out the conversions necessary.
How to convert from Unix time in Power Query for Excel and Power BI
From the Add Column tab on the ribbons in Power Query, select Custom Column. This will allow us to set up a new column in our data based on a calculation or formula.
When we select Custom Column, a new box will open to allow us to enter a formula. We can give our new column a name and enter the formula into the space provided. Remember to replace [Unix time] with the column in Power query that contains the Unix time.
The formula we need to enter when converting from a timestamp recorded in seconds is as follows.
=#datetime(1970,1,1,0,0,0) + duration(0,0,0,[Unix time])
The formula we need to enter when converting from a timestamp recorded in milliseconds is as follows.
=#datetime(1970,1,1,0,0,0) + duration(0,0,0,[Unix time]/1000)
Once we confirm our formula by pressing OK, a new column will appear in Power query showing the date and time.
The last step is to ensure you set this new column to the correct data type. This can be done from the Transform tab in the Power Query Ribbons.
Depending on your need, you can select between
- Date/Time
- Date
- Time
- Date/Time/Time zone
- Duration
How to convert to Unix time in Power Query for Excel and Power BI
Using Power Query in both Excel and Power BI it is easy to convert an Excel or Power BI date and time to a Unix timestamp using a custom column as we did above.
Once we have the date-time column in Power query, Select custom column.
The function we use to convert to Unix time is:
Duration.TotalSeconds([Excel date/time column]-#datetime(1970,1,1,0,0,0) )
(remember to replace [Excel date/time column] with the column that contains the date/time that you wish to convert to Unix.)
Conclusion
The more we work with different data sources in Excel Working with Unix Time in Excel and In Power Query for Excel and Power BI will become more and more common. For this reason, we need to have the ability to convert Unix time to Excel time and Excel time to Unix time. In this article, we looked at using both formulas and Power Query to carry out Unix Timestamp conversions in Excel. If you are working with large sets of data, I would recommend the Power query option, but then if you are working with a large set of data that contains Unix timestamps, most likely you have used Power Query to connect to it in the first place.
Take A FREE course with us Today!
- includes XLOOKUP and will soon include Dynamic Arrays
Power Query Excel 365
Best Value Excel and Excel Power Tool Learning. Access All Areas, Unlimited Learning Subscription
SIGN UP
- includes XLOOKUP and will soon include Dynamic Arrays
Power Query Excel 365
Do you want to start collecting rewards quickly for learning Excel? Then you should try:
SIGN UP FOR OUR NEWSLETTER TODAY – GET EXCEL TIPS TRICKS AND LEARN AND EARN ACTIVITIES TO YOUR INBOX
SIGN UP
I would suggest that you sign up directly for your own hive wallet and use this to sign into the comments below. This way all rewards will be paid directly to your wallet within 7 days. You can use this link to sign up now for your Hive wallet
>> GET HIVE WALLET NOW<<
Have questions? Please use the Hive powered comments section below and we will do our best to help you. Alternatively, you can contact us with this link.
Like what you see? I do hope that you will share this article across your social profiles