### Thread: Convert to/from Unix time

1. #### Convert to/from Unix time

Hi,

I am actually a Perl programmer working 99% of the time on Unix systems. The result is that a lot of times I store date and time data as Integers (Unix timestamps) - either in a file or in a database like MySQL.

Converting date's from MySQL can easily be converted using SQL statements, so no problem there.

The problem that I do have is with CSV formatted files I have that I want to import for our admin people in Excel. One problem though - all date and time values are in Unix time format.

I can convert the CSV files with a Perl filter BEFORE I send them to the relevant people, but to satisfy my own curiosity I was wondering if there isn't an easy way to convert Unix time stamps in Excel - either with a formula or with some VBA.

Any ideas?

Thanks
2. Sometimes it helps when you read the help files

For what it's worth, here is what I discovered:

* Excel stores the date as an integer number with a starting point of 1900-01-01

* Unix time start on 1970-01-01

So I calculated the difference between the two, and came up with 25569.

Time is calculated as a fraction from date. Since I live in South Africa (GMT+2), I need to also add two hours, or 0.08333

Asuming the Unix time stamp is in column A (A1, A2 etc), my formula now looks like this:
Code:
`=(A1/86400)+25569.08333`
For now that seems to work, but any forther notes from the experts will be appreciated.

Cheers
3. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
May 2009
Posts
1
Rep Power
0

#### Converting Between Excel and UTC Time

I have found the following to be accurate for my purposes. Perhaps you can improve on it.
There is some fiddly stuff with TZs because UTC is stored as time at the meridian, but excel dates have no TZ info in my case.
Code:
```Const secondsInADay = 86400#
Const excelUTCDiff = 25569#
Const timezoneAdjust = 10# * 3600#
' utcDate = 1970-01-01 + seconds to current date + TZ offset
' excelDate = 1900-01-01 + days to current date + seconds/seconds in a day
' -10 * 3600 = timezone offset in seconds for Australia
' 86400 = seconds in a day
' 25569 = days b/w 1/1/1900 (excel epoch) and 1/1/1970 (unix epoch)

Public Function UtcDateToExcelDateSerial(ByVal utcDate As Double) As Double
'' Return an excel date serial equivalent to a unix UTC date
Dim retVal As Double
UtcDateToExcelDateSerial = retVal
End Function
Public Function ExcelDateSerialToUtcDate(ByVal excelDate As Double) As Double
Dim retVal As Double
Dim numDays As Double
Dim timePart As Double
'excel date for 1/1/2009T01:01:01 = 39814.042361
' 39814 days since 1/1/1900 + .42361 of a day
numDays = Math.Round(excelDate, 0)
timePart = (excelDate - numDays) * secondsInADay
retVal = (((numDays - excelUTCDiff) * secondsInADay) + timePart) - (10# * 3600#)
ExcelDateSerialToUtcDate = retVal
End Function```
4. Originally Posted by cargo
I have found the following to be accurate for my purposes. Perhaps you can improve on it.
There is some fiddly stuff with TZs because UTC is stored as time at the meridian, but excel dates have no TZ info in my case.
Thanks - I will be testing this soon :-)