How to Convert Excel Time Difference to Decimal Number

Excel 2013 works with time values much the same way as any number, including the ability to subtract times to find the time difference.

Showing a time difference in hours, minutes and seconds isn't always convenient. If you are processing a large number of time differences as part of a testing experiment, for example, the values may be easier to work with if you have Excel convert them to decimal values. The default decimal value is based on a 24-hour unit, however you can change this to a 1-hour, 1-minute or 1-second unit as needed.

Video of the Day

Step 1

Image Credit: Screenshot courtesy of Microsoft.

Open a blank worksheet. Drag the cursor over cells C1, C2 and C3 to highlight them. Click the Home tab and select Time from the Number menu to display the time in hours, minutes and seconds. For other Time format options, like military time, click the small Arrow at the bottom of the ribbon's Number section and select any Time format desired.

Step 2

Image Credit: Screenshot courtesy of Microsoft.

Enter a start time in cell C1 and the end time in cell C2. In cell C3, type =(C2-C1) to subtract the stop time from the start time.

Step 3

The time difference is 4 minutes and 12 seconds.

Image Credit: Screenshot courtesy of Microsoft.

Press Enter to complete the formula. Excel subtracts the start time from the end time.

Step 4

Image Credit: Screenshot courtesy of Microsoft.

Type =(C2-C1) in cell C4 and press Enter. Click the Home tab and change the Number menu to Number. Excel converts the time difference to decimal format, based on one day. For example, 24 hours would be 1.0 and 12 hours would be 0.5.

Click the Increase Decimal icon in the Numbers section of the Home ribbon if the decimal unit is too small to get an accurate result.

Step 5

Image Credit: Screenshot courtesy of Microsoft.

Highlight cells C4 and C5 and change the Number menu to Number.

Type =(C2-C1)*24 in cell C5 and press Enter. Multiplying the time difference by 24 displays the result as a decimal unit based on one hour. For example, 45 minutes would be 0.75 and two hours would be 2.0.

Type =(C2-C1)2460 in cell C6 and press Enter. This creates a decimal unit based on one minute. Alternatively, you could multiply the value by 1440.

To calculate a decimal value based on one second, use =(C2-C1)*86400.

Step 6

A time difference of 2 minutes and 12 seconds.

Image Credit: Screenshot courtesy of Microsoft.

Change the start and end times. Excel automatically recalculates the time difference in each cell you entered a formula.