Convert time span values to decimal values

To convert a time value to a decimal, you can simply change the format. But it isn't so easy to convert a value that represents a measurement of time.

The sheet below shows a simple project-tracking spreadsheet. The expression in column D evaluates two time values and returns the time spent on a project (=C2-B2). This type of calculation is familiar to most of us. You can enter the time values in 24-hour format or AM/PM format, and the expression won't care. So far, so good. Now, let's suppose you want to display the total time expended (column D) as a decimal value, so you change the format to General. Unfortunately, that won't get the results you expect. For instance, in the sheet below, you can see that the General format displays 0.03125, not .75 (45 minutes is the same as three-quarters of an hour). That's because Excel interprets 0:45 as the moment in time that we know as 12:45 AM, not a general time period of 45 minutes. Interestingly, 0.03125*24 equals .75 — that's not a coincidence. There are two ways you can go, depending on your needs. First, the easy way: Simply multiply the totaling expression in column D by 24, =(C2-B2)*24, and apply the General format. Unfortunately, you lose the time-formatted display (0:45). To maintain both values — the time and decimal displays — insert a new column, enter an expression that multiplies the total time in column D by 24, =D2*24, and apply the General format. Although this method requires a bit more work and space, it has the added benefit of easily delivering daily totals using the Subtotals feature as follows:

  1. Select the data (A1:F15).
  2. Choose Subtotals from the Data menu.  Excel will default to the Date value in column A for changing.
  3. If necessary, choose Sum from the Use Function drop-down list.
  4. In the Add Subtotal To list, check both Total and Decimal (you want subtotals for both columns).
  5. Click OK.

    This feature quickly subtotals both the time-formatted and decimal values. Of course, you could enter the Subtotal() functions yourself, but Excel can do it much quicker. Working with time values isn't difficult as long as you know whether the values represent a specific moment in time or a span across time. In this example, we used both. Columns B and C contain time values. Column D displays the amount of time between those two time values. Note: The simple expression in column D can't handle time periods that stretch across two or more days unless the time values contain both the date and time. In other words, if the project starts on Monday and extends past midnight into Tuesday, you must enter both the dates and times for this expression to work.

    By Susan Harkins

    Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.