How to Convert Excel Time Difference to Decimal Number

By David Weedmark

Learn how to find the difference between a start time and end time and then convert that number into a decimal format using Excel 2013.

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.

Step 1

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

Subtract cell C1 from C2.

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.

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

Step 4

Select Number from the Number menu.

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

Calculating a decimal value based on one minute.

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.

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

Tip

To convert a time to a decimal value when you don't need to calculate the time difference as well, you can simply enter the time as part of the TIMEVALUE function. For example, entering =TIMEVALUE("2:24 AM") in a cell returns a decimal value of 0.10 based on a one-day unit.