The Old Blog Archive, 2005-2009

Archive for February, 2009

That 1904 Thing

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 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).

Pasting 1900-Based Date Data to 1904-Based Worksheet: The Problem

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).

Pasting 1900-Based Date Data to 1904-Based Worksheet, Step 1 & 2

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.

Pasting 1900-Based Date Data to 1904-Based Worksheet, Step 3.1
Pasting 1900-Based Date Data to 1904-Based Worksheet, Step 3.2

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

Pasting 1900-Based Date Data to 1904-Based Worksheet, Step 4 and 5

Step 5. Click Ok.

Step 6. You can delete the 1462 cell now.

Pasting 1900-Based Date Data to 1904-Based Worksheet, Step 6

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®,, 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.

TapExpense 2.0.1 Available

TapExpense 2.0.1 is now available on the App Store. This is a minor update but fixes a nagging bug that caused trouble when you tried to add a new category (or currency, group etc.) to a reordered list. This is now fixed.

It’s good to see our app come back to the market after first a hiatus then some long weeks of rewrite. We’re doing well on App Stores Italy and Japan (TapExpense is now in the Finance Top 20 Paid Apps List in both stores), and now in the Top 5 Financial Paid Apps List on App Store Taiwan (it’s actually in the Top 100 Paid Apps, All Categories list, too). Still, software development does not stop there. Unlike a finished book or a recorded music, software is organic in the sense it continues to grow after a release version is pushed out of the door. We’ll continue working on TapExpense to make it better.

TapExpense: The Making of a 2.0

After months of working and reworking, I’m happy to announce that TapExpense 2.0 is now available on the App Store.

TapExpense is an expense tracker for iPhone and iPod Touch. Version 1 started with a simple design. It supported multiple currency (a major feature) and could export data as CSV-formatted email text. Version 2 is a rewrite. It now exports XLS spreadsheet files. It also supports income categories, vendor/payee/source field, and group/trip field. It now uses a numeric keypad to speed up entering records, and has a passcode lock that lets you show off your other iPhone apps while keeping personal finances discreet.

All the new features, while keeping the fundamental design that aims to keep the balance between workflow simplicity and feature richness.

And we have a gorgeous icon too!

The road to 2.0 was a long journey. My colleagues and I have learned a lot from the process. TapExpense 1.2 was the expense tracker I enjoy using everyday. TapExpense 2.0 is a software product I’m proud of and I want to tell everyone about it.

So if you’re an iPhone or iPod Touch owner, and if you’re looking for an expense tracker, reading this blog entry, be sure to check it out and give it a try.

We even have a lite version that has every feature of the paid version and is fully functional. It neither expires or annoys. The sole limitation is that it only shows, reports and exports the latest 7 entries in any given date range. And you can continue using it adding as many entries as you like. Later, if you decide this is the application for you and you buy it, you can always transfer all your entries in the lite version to the paid version. It’s just that easy.

In the coming weeks I’m going to write more about TapExpense 2.0, about the new features in detail, about the nuance in the decisions of adding or dropping a feature, and about what we have learned from our users and our own usage.

Before then, find out more on the App Store: TapExpense, TapExpense Lite. And Lithoglyph company website. The paid version is USD 4.99 (or the equivalent of it in Europe and other stores). Existing user gets free upgrade.

And let us know how we can make it serve you better too.

Thank you!