- #VISUAL BASIC FOR MAC EXCEL 2011 CODE#
- #VISUAL BASIC FOR MAC EXCEL 2011 MAC#
- #VISUAL BASIC FOR MAC EXCEL 2011 WINDOWS#
To convert dates, I naturally followed the same plan I did in the sheet’s cells: get the value for, then add the unix epoch days.Īnd the dates came out different. So away I went, coding with a twinkle in my eye and a song in my heart.
#VISUAL BASIC FOR MAC EXCEL 2011 MAC#
The good news: Mac Excel 2011 uses Visual Basic for Applications (VBA), which while imperfect is a zillion times better than AppleScript. I also have some more fancy work to do that requires scripting.
#VISUAL BASIC FOR MAC EXCEL 2011 WINDOWS#
(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. The formula then adds on the number of days from the spreadsheet zero to the UNIX zero time. 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. Where the unix time is in cell Z3 of the ‘import’ sheet.
#VISUAL BASIC FOR MAC EXCEL 2011 CODE#
Here’s the code I use in a cell of the spreadsheet that needs to show a converted date: 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. The dates in my data are based on the UNIX epoch, so I need to convert them. 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. Mac Excel dates aren’t burdened by the bogus day. 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?). Oh, except that Microsoft time doesn’t always start in 1900, and here’s where things start to get squirrely. A thousand years from now we may still be calculating time based on the insertion of a bogus day. Every date in every spreadsheet would suddenly be off by one. To change it now would cause problems, because the zero point for the Microsoft time is January 1, 1900. Microsoft included this error because they wanted to be compatible with Lotus, who simply messed up. Of course, there are a couple of caveats. (As long as you don’t need dates before the zero time. So good on Microsoft for coming up with it. I haven’t checked, but I think this system works for dates far, far into the future. 12.5 represents noon twelve days after the zero point. Excel counts in days, but allows fractional values. That’s all well and good, but I’m here to discuss Microsoft Excel today, and in particular Microsoft Excel for Mac. (Note also that you can only go back a finite distance before the negative number goes out of the range the processor can handle. 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. You get special Geek Cred if you went to a party to celebrate second 1234567890 of the Unix epoch. The UNIX boys count seconds from Januat 00:00. I do remember that the number gets too big for the computer to handle some time in 2014. I don’t remember anymore exactly when the zero point was, or even what the units chosen were. My first exposure to a more rational way to measure time was in the old MacOS.
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. Remember them? A number line stretches from zero to infinity in both directions. Therefore long ago people who were smarter than I am came up with other ways to represent time. Using strings like “” to represent a date has plenty of drawbacks, from cultural (is that March 10th or October 3rd?) to performance-related (sort, , ).
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. How do they manage this? They take a good engineering solution, then fiddle with it. Microsoft Excel uses a method to represent time that is both smart and frustrating. You can skip ahead or read my brilliant and entertaining *cough* analysis first. 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. Note to people looking for a formula: Yes, the code is here (for Mac and Windows, even).
0 kommentar(er)
