Note to people looking for a formula: Yes, the code is here (for Mac and Windows, even). I tend to go long-winded even in technical articles, but if you’re dealing with converting UNIX time to Excel time, the answers lie below. You can skip ahead or read my brilliant and entertaining *cough* analysis first.
Microsoft Excel uses a method to represent time that is both smart and frustrating. How do they manage this? They take a good engineering solution, then fiddle with it. First some background:
Long before Y2K people who knew what they were doing had already abandoned the practice of using strings of text to represent dates in a computer. Using strings like “3/10/2011” to represent a date has plenty of drawbacks, from cultural (is that March 10th or October 3rd?) to performance-related (sort 3/10/2011, 4/2/1902, 3/8/2012). Therefore long ago people who were smarter than I am came up with other ways to represent time. Happliy, time is nice and linear. All you really need is a number line. Remember them? A number line stretches from zero to infinity in both directions. To measure time all you need to do is decide on a zero point and then any point in the history of the universe can be represented by some number of time units from that instant.
My first exposure to a more rational way to measure time was in the old MacOS. I don’t remember anymore exactly when the zero point was, or even what the units chosen were. I do remember that the number gets too big for the computer to handle some time in 2014. Ancient Macs will have a problem then. I blame the Aztecs.
The UNIX boys count seconds from January 1, 1970 at 00:00. You get special Geek Cred if you went to a party to celebrate second 1234567890 of the Unix epoch. 32-bit computers that use the Unix epoch will break in 2038, when the number of seconds gets too big to fit in 32 bits. (Note also that you can only go back a finite distance before the negative number goes out of the range the processor can handle.
That’s all well and good, but I’m here to discuss Microsoft Excel today, and in particular Microsoft Excel for Mac. Excel counts in days, but allows fractional values. 12.5 represents noon twelve days after the zero point. I haven’t checked, but I think this system works for dates far, far into the future. So good on Microsoft for coming up with it. (As long as you don’t need dates before the zero time. In Excel, those are just strings again).
Of course, there are a couple of caveats. First: the historical oddity. In Excel, the day February 29, 1900 exists. Alas, there never was such a day. Microsoft included this error because they wanted to be compatible with Lotus, who simply messed up. To change it now would cause problems, because the zero point for the Microsoft time is January 1, 1900. Every date in every spreadsheet would suddenly be off by one. A thousand years from now we may still be calculating time based on the insertion of a bogus day.
Oh, except that Microsoft time doesn’t always start in 1900, and here’s where things start to get squirrely. If you’re using Excel for Mac, the default day zero is January 1, 1904, so the bogus day vanishes (no negative dates in Excel, remember?). Mac Excel dates aren’t burdened by the bogus day. Except when they are. More on that in a bit.
I descended into Excel recently to write a macro that does fancy formatting based on data I dump from a Web-based tracking tool I’m building. The dates in my data are based on the UNIX epoch, so I need to convert them. I dump the raw data into one sheet and then display it correctly converted and formatted on the main sheet that people actually look at. Here’s the code I use in a cell of the spreadsheet that needs to show a converted date:
=DATE(1970,1,1)+import!Z3/(60*60*24) |
where the unix time is in cell Z3 of the ‘import’ sheet. This divides the unix time by the number of seconds in a day, which gives me the number of days that have passed since the UNIX zero time. The formula then adds on the number of days from the spreadsheet zero to the UNIX zero time. (I could just say 86,400 instead of 60*60*24, but this way I can tell at a glance I’m dealing with days, and speed will not be an issue.) Happily, this formula will work on both Mac and Windows versions of Excel, because the DATE function will return the right value for the start of the unix epoch based on whichever version of Excel is running.
So, no problem, right?
Well… except. I also have some more fancy work to do that requires scripting. The good news: Mac Excel 2011 uses Visual Basic for Applications (VBA), which while imperfect is a zillion times better than AppleScript. So away I went, coding with a twinkle in my eye and a song in my heart. To convert dates, I naturally followed the same plan I did in the sheet’s cells: get the value for 1/1/1970, then add the unix epoch days.
And the dates came out different. Yep, when scripting, Excel always uses the Windows zero time, even when the spreadsheet in question uses the Mac zero time. Dates calculated in cells in the sheet are four years different than dates calculated using the same method in a script.
Aargh. Of course, once I realized what the problem was, it was not too difficult to work around it. I just lost some of the portability of my code, because now it has to be tweaked based on what the zero date of the spreadsheet is.
An aside here: If you’re here to resolve some date confusion in your own Mac spreadsheet, I strongly recommend you start by going to Preferences->Calculation->Workbook Options and uncheck the “use 1904 date” option. Unfortunately it will not recalculate the dates already entered in your sheets, so if that’s a problem then it’s too late for you, bucko. Read on.
Here’s some not-as-portable-as-it-could-be code. You need to choose one of the first two lines based on whether your sheet uses mac dates or windows dates:
'excelZeroDate = DateSerial(1904, 1, 1) ' mac zero excelZeroDate = 0 ' DateSerial(1900, 1, 1) ' windows zero unixZeroDate = DateDiff("d", excelZeroDate, DateSerial(1970, 1, 1)) |
Then if I have a date in the ‘import’ sheet to convert, I can write something like:
startDate = DateAdd("s", Worksheets("import").Cells(dateRow, dateColumn), unixZeroDate) |
The nice part is that these functions handled converting seconds and days for me. Overall it’s not a bad system if you overlook the part where a single application gives two different answers to the same question.
