By Dan Moren

July 16, 2015 11:30 AM PT

Numbered days: Freelance accounting with Apple’s spreadsheet

Note: This story has not been updated for several years.

Part of the challenge in going freelance over the last nine months or so has been keeping careful track of the projects I do, and making sure not only that I remember to send out invoices, but also that I get paid—hopefully, promptly.

I’ve tried out a few all-in-one solutions that have been recommended, including FreshBooks and Billings Pro, and while they’re almost all perfectly fine, they’re also generally far too much for my needs. I deal only with a few clients, and most of my work does not require time-tracking.

What I really wanted was a Mac app for simple databases, à la the late, lamented Bento. There are a couple of spiritual successors out there, including Records—still a bit too light on features for me—and Tap Forms Mac—much more powerful, but a little idiosyncratic for my tastes.

Well, as they say, if you want something done right, do it yourself.

Ultimately, the best solution for me—and I stress, for me—has proved to be Numbers, for a few reasons. Firstly, it’s already on all my Macs and iOS devices. Secondly, while it may not be on par with the likes of Excel, it is remarkably powerful. And thirdly, well, it’s pretty and has a pretty gradual learning curve.

I figure some of what I’ve done in concocting my master freelance spreadsheet might be of interest, both to folks who have to track this kind of information, as well as other novice-to-intermediate Numbers users. (As a caveat, I’m hardly an expert on these subjects, so I’m sure there are more efficient or better solutions to some of these problems—this is simply the way I did it.)

What have I wrought?

Here are a few of the techniques I used to up my spreadsheet game:

If statements: The payment status of my projects is perhaps the most important thing that I track and here a little logic goes a long way. I’ve got four status conditions: Unsubmitted, Unpaid, Paid, and Overdue.

While I could have entered and updated these statuses manually, I determined fairly simple rules by which they could be programmatically interpreted.

• Whenever I start a new project, I enter a new item. Until I submit an invoice, the Date Submitted field remains blank, which Numbers knows means that project is Unsubmitted.
• When I do submit an invoice, I add the date I did so in the spreadsheet, which flips the status to Unpaid.
• When I receive payment, I check the respective checkbox in the Paid column, which flips the status to Paid.
• Finally, if an item is Unpaid and it’s been 30 days since the submitted date, the status changes to Overdue.

Here’s the formula I use (with “C” being the Date Submitted column and F being my Paid column):

IF(ISBLANK(C3),"Unsubmitted",IF(AND(F3=FALSE,DATEDIF(C3,NOW(),"D")≥30),"Overdue",IF(F3=TRUE,"Paid","Unpaid")))

I can also use that to automatically track the total amount of outstanding payments—i.e. how much money I’m owed from clients—by creating a cell at the top of this sheet with this formula (Paid and Amount are column names):

SUMIF(Paid,FALSE,Amount)

Conditional formatting: The status field is handy, but it’s not particularly glanceable. In order to make it easier to quickly skim my records, I decided to color code the status field. Green for Paid, yellow for Unpaid, orange for Unsubmitted, and red for Overdue.

Under Numbers’s Format inspector, you can click Conditional Highlighting to create rules that change a cell’s appearance depending on certain conditions. In this case I simply matched against the text of the statuses and changed the fill of the cells. Easy peasy.

Lookup: Anybody who’s spent time with a spreadsheet, whether it be Google Sheets or Microsoft Excel, knows the power of LOOKUP functions. Essentially these let you pull information from other cells, even if they’re on other sheets in the same document.

In my particular case, I wanted to automatically generate invoice identifiers. I usually format my invoice IDs by using a unique two-letter code that signifies the client, followed by the two digit month number and the last two digits of the years. So, for example, if I were doing work for Tony Stark in January of 2016, my invoice for him would be TS0116.

Well, based on the date the invoice is submitted, it should be easy enough to pluck the month and year information.¹ For the client code, it was simply a matter of creating a separate sheet which listed my client names and their respective codes, and then using LOOKUP to pull the right code for the listed client.

I actually ran into some tricky issues here. For one, it refused to match one of my clients accurately until I realized that it had an apostrophe, which sometimes got rendered as a “smart” (or “curly”) apostrophe and sometimes as a straight one—and those two characters won’t technically match. (I fixed that by going through manually and standardizing them.)

Secondly, while Numbers has functions for pulling the month number and year from a date string (MONTH and YEAR, respectively), the formats weren’t quite right for my invoice ID. I couldn’t figure out how to tell it to format the month with a leading zero, so I had to add an IF statement that checked if the month was less than 10, and if so, added a 0 in front. Likewise, the YEAR function returns a four-digit year, when I just wanted the last two, so I used the RIGHT function to truncate it to just two digits.

My final formula (once again “C” is the column containing the date, and “D” is the column containing the client’s name)²:

<

pre>CONCATENATE(LOOKUP(D12,Client Codes::Table 1::Client,Code),IF(MONTH(C12)<10,”0″&MONTH(C12)),RIGHT(YEAR(C12),2))

Charts: This last was purely for my own edification. I was curious to know where my money was coming from, so I decided to make a pie chart breaking down my income by client. Also, Numbers—as previously referenced—makes pretty charts.

Fortunately, it’s pretty easy with Numbers’s Chart tool. I just selected the pie chart from the toolbar and then edited the provided data values table: on the left I entered the name of each of my clients, on the right a simple formula that summed up all of the entries for that client.³ (“A” in this case references the client name from the first column.)

SUMIF(Invoice Status::Client,A2,Amount)

What else can I do?

I’m not done perfecting this quite yet; I’m looking for further ways to bend Numbers to my will. I’d love to have it start automatically generating invoices for me, but I’m not sure yet if that’s beyond its capabilities. It’s entirely possible that at some point what I’m doing will outgrow this one single Numbers file, but for now it seems to handle my modest freelance employment with aplomb and a minimum of fuss.

[Dan Moren is the East Coast Bureau Chief of Six Colors. You can find him on Mastodon at @dmoren@zeppelin.flights or reach him by email at dan@sixcolors.com. His latest novel, the supernatural detective story All Souls Lost, is out now.]

If you appreciate articles like this one, support us by becoming a Six Colors subscriber. Subscribers get access to an exclusive podcast, members-only stories, and a special community.