Explanation
The Unix time stamp tracks time as a running count of seconds. The count begins at the “Unix Epoch” on January 1st, 1970, so a Unix time stamp is simply the total seconds between any given date and the Unix Epoch. Since a day contains 86400 seconds (24 hours x 60 minutes x 60 seconds), conversion to Excel time can be done by subtracting the date value for the Unix Epoch and multiplying days by 86400.
In the example shown, the formula first subtracts the date value for January 1, 1970 from the date value in B5 to get the number of days between the dates, then multiplies the result by 85400 to convert to a Unix time stamp. The formula evaluates like this:
=(B5-DATE(1970,1,1))*86400
=(43374-25569)*86400
=1538352000
How Excel tracks dates and time
The Excel date system starts on January 1, 1900 and counts forward. The table below shows the numeric values associated with a few random dates:
| Date | Raw value |
|---|---|
| 1-Jan-1900 | 1 |
| 28-Jul-1914 00:00 | 5323 |
| 1-Jan-1970 00:00 | 25569 |
| 31-Dec-1999 | 36525 |
| 1-Oct-2018 | 43374 |
| 1-Oct-2018 12:00 PM | 43374.5 |
Notice the last date includes a time as well. Since one day equals 1, and one day equals 24 hours, time in Excel can represented as fractional values of 1, as shown in the table below. In order to see the value displayed as a time, a time format needs to be applied.
| Hours | Time | Fraction | Value |
|---|---|---|---|
| 3 | 3:00 AM | 3/24 | 0.125 |
| 6 | 6:00 AM | 6/24 | 0.25 |
| 4 | 4:00 AM | 4/24 | 0.167 |
| 8 | 8:00 AM | 8/24 | 0.333 |
| 12 | 12:00 PM | 12/24 | 0.5 |
| 18 | 6:00 PM | 18/24 | 0.75 |
| 21 | 9:00 PM | 21/24 | 0.875 |
| 24 | 12:00 AM | 24/24 | 1 |
Explanation
The core of this formula is the DATE function, which is used to assemble a proper Excel date value. The DATE function requires valid year, month, and day values, so these are parsed out of the original text string as follows:
- The year value is extracted with the RIGHT function:
RIGHT(B5,2)+2000
RIGHT gets the right-most 2 characters from the original value. The number 2000 is added to the result to create a valid year. This number goes into DATE as the year argument.
- The month value is extracted with:
MID(B5,4,2)
MID retrieves characters 4-5. The result goes into DATE as the month argument.
- The day value is extracted with:
LEFT(B5,2)
LEFT grabs the final 2 characters of the original text value, which goes into DATE as the day argument.
- The three values extracted above go into DATE like this:
=DATE(2016,"02","29")
Although month and day are supplied as text, the DATE function automatically converts to numbers and returns a valid date.
Note: the year value 2016 was automatically converted to a number when 2000 was added.
Dealing with extra space
If the original text value contains extra leading or trailing space characters, you can add the TRIM function to remove:
=DATE(RIGHT(TRIM(A1),2)+2000,MID(TRIM(A1),4,2),LEFT(TRIM(A1),2))
