Formatting times in Excel

I tend to suggest that my students use MS Excel to analyse their qualitative data transcripts. While Excel is not perfect, it has the advantage of being cheap, relatively easy to use, and a well-known and transportable format.

There are some tricky elements to working with Excel. One is that times entered into Excel have a nasty habit of turning from minutes counted (such as the duration of an interview) to clock time. If you are trying to total interview minutes, and the data keeps changing format, all totals have to be checked very, very carefully. In addition, since moving to Office 365, I have had problems with Excel's custom number formatting disappearing after updates.

As a result, it is worth detailing how to format time in Excel to get total minutes, as opposed to clock time. The instructions are

• On the home ribbon in Excel, click the expansion arrow under the numbers section:
  
  
  
• The dialogue box which appears will show number formats, and be selected on the General number type:
  
  
  
  
  
• Change the number format to “Custom”, at the bottom of the list:
  
  
  
  
  
• Under the Type heading, General is the default number format. Highlight this, as we are going to replace it:
  
  
  
  
  
• Key the following format code into the Type box (thanks to Bigoceans, 2012):

  [h]:mm;@

  
  
  
• Click OK.

Time should now add as interview time; in hours, minutes and seconds – no longer as clock time.

Sam

• Reference: Bigoceans (2012). Excel 2007: How to display mm:ss format not as a DateTime (e.g. 73:07)?. https://stackoverflow.com/questions/11095994/excel-2007-how-to-display-mmss-format-not-as-a-datetime-e-g-7307/11128380