19 December 2016

Calculate age in years (and months) in Excel

It is often desirable to calculate a person's age from their date of birth relative to some other date (e.g., the date when they completed some test). This is pretty easy to do in Excel.

Calculating age in years
The simplest way to do this is using the following formula


where start_date is the cell containing the person's date of birth.

Using the above formula for someone with a D.O.B. of 20/3/1990 who was tested on 19/9/2016 will return the value 26.5017238, which may be reduced to fewer decimal points if so desired.

Calculating age in years and months
To calculate age in years and months, rather than using a decimal point, use the following formula

=concatenate(datedif(start_date,end_date,"Y"," years, ",datedif(start_date,end_date,"YM")," months")

Using this formula will return the value 26 years, 5 months. This formula can be modified to return other commonly used formats such as 26;5.

11 June 2015

How to set up Tweetbot iCloud sync across iOS and OS X

Tapbots recently released their much anticipated v.2 update for their Mac OS X Yosemite version of their Twitter client, Tweetbot. One of the most touted features is the ability to sync timeline position across devices using iCloud. However, a quick Google search reveals that many people are experiencing issues with getting iCloud sync to work. Some of the more detailed guides I came across are:

How To Get Tweetbot to Sync to iCloud on iPhone (Fix) via Apptactics

Tweetbot and that f*cking iCloud sync via Coupaman's New Digs

Unfortunately, neither of the above guides solved my iCloud timeline sync issues. In the end, the thing that fixed the problem was signing out of iCloud on my iPhone and then signing back in.


1. In iOS, tap Settings, then iCloud.

2. Scroll down to the bottom and tap Sign Out, and then confirm when prompted.

3. Sign back in to iCloud.

That's what did it for me. I hope that this is helpful for those who are experiencing similar issues.

09 December 2014

How to fix the no sound issue on a MacBook

I have been experiencing some sound output issues on my MacBook Air recently, and a quick Google search revealed that many people have been experiencing the same problem. Here is a short description of the the problem and my solution.

What's the problem?
I like to use ear phones with my MacBook. The sound works fine when I first boot up, however, if I am away from the computer and it goes to sleep (e.g., during lunch), the volume icon is greyed out when I wake the computer. The sound does not work and I cannot adjust the volume. Very annoying.

Sound is affected on a system-wide level. This is not restricted to a single app. Rebooting the computer resolves the issue, but this is not something I want to be doing all the time. Other users recommend flushing PRAM and doing all sorts of weird things in the Terminal that I don't want to be part of my regular workflow. Ideally, I'd like to do something once that fixes the problem for good.

The solution
When the volume icon is greyed out, click System Preferences | Sound | Output and select Headphone port as your device for sound output. That's it.

And there you have it. A simple solution to a persistent and complex problem.

30 October 2013

How to disable Protected View and Enable Editing button in Office 2010

A very annoying feature in Office 2010 is the requirement to click Enable Editing when documents are opened in Protected View from 'untrusted sources'. It raises so many false alarms that it is ignored by most users and therefore fails to achieve the very thing that it was designed to do, namely security. Here is how to turn it off:

Click File | Options | Trust Center | Trust Center Settings... | Protected View, which will bring you to this

click for larger view

Uncheck Enable Protected View for files originating from the Internet, and then click OK.

You will have to do this in Word, then again in Excel etc. to get it to work in each Office app.

31 May 2013

How I handle many projects at the same time

A colleague recently contacted me asking if I could recommend a particular project management software. Here is the question:

Hi Mark, hope all is going well for you in HK. Just wondering if you have tried a project management software? I have so many facets running in my project I need to create something that tracks each aspect. Something that I can share with others would be good. Thought you might have needed something similar?

There are literally thousands of articles devoted to project and productivity systems and apps. I don't use project management software, per se. I'm not even sure what different people mean when they say project management, that is, what their requirements are. I thought that I'd give you a brief overview of how I work, because the system is designed in such a way that I always know where everything is and what is or isn't up to date for each project.

I keep all of my work in a folder called MarkA. So when I backup that folder, I know that I've got everything. Having moved around the world a few times, trust me it makes a big difference to know that you've got everything after backing up a single folder. Within that folder are the following folders: Comments, Ethics, Experiments, Grants, Papers, Payments, Presentations, Reviewing, Supervision, Teaching. There are some others, but those are the main ones. Let's take the two most important as examples: Experiments and Papers. Within Experiments there is a subfolder for each of my research projects. For example, I have a project in which we teach older adults a second language, and it lives in a subfolder called L2AGE. Within that folder are the experiment files and stimuli, and for each project I usually have 2-3 subfolders:
1. I always have a folder called ~archive which stays at the top and contains any old files - I never ever ever delete anything.
2. I have a folder called Results which contains all data and an Excel sheet with the processed data and figures etc. Once data goes into the excel sheet, I usually archive the raw experiment files.
3. For some projects I will have an additional folder called StimDev which contains the files that I used to develop the stimuli and some notes about how I did it which will eventually go in the method section of the paper.

That brings me to writing up papers. I have a folder called Papers in the top level MarkA folder, and within it is a subfolder for each paper that I am writing, the names of which usually correspond to the studies in the Experiments folder, but not always (e.g., there might be an L2AGE folder in the Papers folder too). Within that there will be different versions of the paper, e.g., "L2AGE 01ma.doc", where L2AGE is the name of the paper, 01 is the first draft, and ma are my initials. So, if I send it to a coauthor and I get her comments, I will mark that version L2AGE 02xx (where xx are her initials), and then work on L2AGE 03ma and so on. Importantly, I always have the latest version of the manuscript in my Dropbox folder so that I can work on it wherever I am without needing to sync, and email the latest version to anyone who may request it using the Dropbox app on my phone - so in a way, the files in my Dropbox folder are the current things that I'm working on. As a general guide, I end up with about 15-25 versions of a paper before it is published. Once it is in press, I remove the final version from Dropbox and put it in its subfolder within /MarkA/Papers/.

In terms of gathering references for each paper, I use a reference software called Zotero. For each project, I have a folder in Zotero, e.g., L2AGE, and when you click on it, only the relevant references are displayed. This makes adding references in Word easy, or generating a bibliography for somebody easy as well.

In terms of tracking the progress of each project, I use a Google Spreadsheet to update my boss on a weekly basis about what has been achieved in the past week or if I have any questions or require him to do anything. It is very simple: three columns 1. Project, 2. Update, 3. Boss' comments, and one row for each project (continuing our example from above, L2AGE would have its own row). An example of an update might be "haven't heard from ethics yet, been 2 months" to which he might reply "remind me to bug them tomorrow". That's it. Short communication. Another example might be "data from 5 new Ss" to which he might reply "present" meaning that I will show the data of the 5 new subjects at our next meeting. So, I would go into C:/MarkA/Experiments/L2AGE/Results/ and make sure that all data has been processed and the Excel sheet L2AGE.xls is up to date.

I have a notebook that I take with me to all meetings. I write the name of the meeting and the date and take notes that I will need to remember in future. These include quick jobs and tasks as well as longer term plans and goals. My note taking ability is one of my strengths, I think because I try to include context and information so that I will remember what the note was about even in a year's time when I have forgotten what the meeting was about.

Sometimes if I have a lot going on in terms of little jobs that need doing, I will transfer the tasks to a todo app (called Wunderlist) that automatically syncs between my computer, phone and iPad. The list usually contains things that I don't regularly do and don't want to forget, like reminders about what to ask in a meeting when I am visiting somewhere, or following up with a vendor by a certain date about a particular issue. I don't bother making todos for things like "write journal paper". I know I have to do it, and won't forget. To give you an idea, I haven't written a single todo for 3 weeks because I've been working on papers. The google spreadsheet updates (and increasing version numbers) are enough.