fiat lux

When we were planning for TapExpense 2.0, we have made the decision to make Excel® spreadsheet export the No. 1 priority. This was harder than we had thought.

An expense tracker without export function is quite a dead box. That’s why almost every iPhone expense tracker you find in that category provides CSV export. CSV stands for comma-separated values. It’s a very simple, plain-text format. Each line of the file represents a row, columns of which are in turn separated by commas, hence the name.

Different iPhone expense tracker provides different way of exporting. TapExpense mails the CSV to you as the body text of an email. This requires no installation of other third-party software (as some competitors do). On the other hand, you need to open the email client on your desktop computer, copy-and-paste the body text into another text file, save it as a .CSV file, then open it up in your spreadsheet program. It’s a bit of work.

That’s why we decided that the best way is to export directly to Excel spreadsheet file, mail the file to you. You can then open the file in your spreadsheet program, preview it in your Mail.app or Mobile Mail, or if you use Gmail, you can view and use the spreadsheet directly. CSV does not have such convenience.

If you’re a Windows iPhone user, that’s the end of the journey. If you’re a Mac user, using Microsoft Office for Mac, you might want to read further to understand a curious design in Excel called the 1904 problem. This is important when you try to copy-and-paste the exported spreadsheet into one of your existing workbook.

A Very Short History

As it turns out, Excel actually has two date systems. One is the 1900 date system, another the 1904 system. Why there are two systems is purely historical (see this Microsoft article for details). When Excel first came out on PC, it had to support the date system that was used by Lotus 1-2-3, then the most popular spreadsheet program (later dethroned by Excel). Excel for Mac was not designed to have such compatibility in mind, so the 1904 date system was used instead. A detailed (and more technical, programmer-oriented) explanation can be found in this excellent blog entry by Joel Spolsky.

So How Does It Affect Me?

Excel’s two date systems have an inconvenient consequence: You cannot copy-and-paste the dates from a spreadsheet using the 1900 date system to another spreadsheet using the 1904 date system (and vice versa). To make things worse, there is no way you can save a 1900-based spreadsheet file as another 1904-based file (and vice versa). There are both legitimate and historical reasons for Excel not to do the conversion for you, so we are not here to blame any one at Microsoft.

Now here’s the thing: TapExpense exports 1900-based Excel spreadsheet files.

Because a majority of Excel spreadsheets in the world are created by Excel on Windows, and if you’re a Mac users, chances are that you open up a lot of Excel files created by Excel on Windows. To ensure the maximum interoperability, we have decided to use the 1900 date system.

But! If you want to integrate the data on the exported file to your own workbook created by Excel for Mac, you’ll immediately notice that every date that is pasted to your workbook is advanced by 4 years and one day. That’s no good! (Click for enlarged version of picture).

The Solution

Fortunately, all is not lost. If you are pasting data from TapExpense-exported worksheet to your workbook that is created by Excel for Mac, and you found the dates are incorrectly advanced, here’s what you need to do:

Step 1. Enter the value 1462 in an empty cell (any place will do). It may appear as 1/2/08, which is fine (click for enlarged pictures).

Step 2. Select the cell, the press CMD-C or click on the menu Edit > Copy.

Step 3. Select the dates you want to correct. Then click on the menu Edit > Paste Special.

Step 4. A Paste Special dialog box shows up. Click the option Values, then click on the Substract option.

Step 5. Click Ok.

Step 6. You can delete the 1462 cell now.

This in essenece substracts 1462 from every pasted date cell. 1462 is the difference between 1900/1/1 to 1904/1/1. That’s it!

(Some of you might have noticed that 1462 = 366 + 365 + 365 + 366, but 1900 is not a leap year! You’re right, but that’s an unfortunate bug in Lotus 1-2-3, it’s a fait accompli and the 1900 date system will stay that way.)

The above-mentioned Microsoft article also has step-by-step explanations on how to work with the two different date systems.

What We Have Also Taken Care of

The Excel spreadsheet that TapExpense exports from your income/expense records uses the regional settings on your desktop computer. These regional settings are observed by all major spreadsheet programs (Excel, Numbers®, OpenOffice.org, etc.) So if your use the German format in Germany, the amounts will be in the format of 1.234.567,89 (period for the thousand separator, comma for decimal point). The dates are also properly formatted (so 12/31/09 in the US format becomes 31.12.2009 with the configuration above).

In fact, if you prefer the CSV format, you’ll notice that TapExpense 2 also does the CSV right–dates correctly formatted, amount numbers using the right separators, and your Excel or Numbers will understand it when you also use the same regional settings as your iPhone or iPod Touch. These are some of the details we have taken care of when we were working on TapExpense.

We’d Like to Hear From You

So in short, if you are a Windows user, or if you work with Windows-created Excel spreadsheets (you can also set your Excel for Mac to use the 1900-base as the default date system), there’s nothing you need to do when you copy the date from TapExpense-exported sheets. If you need to copy date to a 1904-based workbook, just follow the simple steps above, and all will be fine.

That’s pretty much about it. Do you have any suggestion that we can make it better? We have thought about an option (such as “export 1904-based workbook”) in TapExpense, but given the length the extra explanation might have to get into, that does not seem to be a good design. Or does it? We’d like to hear from you.

lukhnos :: Feb.23.2009 :: tekhnologia :: 3 Comments »