Why you need decimal time in Excel

Excel stores time as a fraction of 24 hours, which works fine for displaying 2:30 PM on a calendar. But when you need to add up hours worked, calculate overtime, or bill by the hour, that format breaks down. If you worked 2 hours and 30 minutes, Excel shows it as 2:30, but you cannot multiply that by an hourly rate and get a sensible answer. Converting to decimal — where 2 hours and 30 minutes becomes 2.5 — lets you do math with your time entries.

The conversion is straightforward once you know which formula to use. Excel has a built-in way to handle this, and the result goes into a regular cell where you can use it in calculations, copy it down a column, or reference it in other formulas.

Key Takeaways

  • Multiply a time value by 24 to convert it to decimal hours — a cell containing 2:30 becomes 2.5 when you use the formula =A1*24.
  • The original time format does not change; the formula creates a new decimal number in a different cell.
  • You can use decimal hours directly in payroll calculations by multiplying the decimal result by an hourly rate.
  • If you have start and end times instead of a duration, subtract the start time from the end time first, then multiply by 24.

Converting a single time entry to decimal

Open your spreadsheet and locate the cell containing the time you want to convert. For this example, assume the time 2:30 is in cell A1. Click on an empty cell where you want the decimal result to appear — say, cell B1.

Type the formula =A1*24 and press Enter. The cell now shows 2.5. That is the decimal equivalent of 2 hours and 30 minutes. If your original time was 1:15, the result would be 1.25. If it was 3:45, you would see 3.75. The pattern is always the same: multiply by 24 because Excel internally stores time as a fraction of a 24-hour day.

Converting a column of times at once

If you have multiple time entries in column A — say, rows 1 through 10 — you do not need to enter the formula ten times. Click on cell B1 and enter =A1*24, then press Enter. Now click on B1 again to select it.

Look for the small square at the bottom-right corner of the cell (called the fill handle). Click and drag that square down to B10. Excel copies the formula down the entire column and adjusts the cell reference automatically — B2 will contain =A2*24, B3 will contain =A3*24, and so on. All your times are now converted to decimal in column B.

Converting times calculated from start and end times

Sometimes you do not have a duration already entered; instead, you have a start time in one column and an end time in another. For example, start time 9:00 AM in A1 and end time 2:30 PM in B1. You need to find the difference first, then convert that difference to decimal.

Click on an empty cell, say C1. Type the formula =(B1-A1)*24 and press Enter. This subtracts the start time from the end time, giving you the duration, then multiplies by 24 to convert to decimal. The result is 5.5, meaning 5 hours and 30 minutes of work. If your end time is on a different day (for example, a night shift that runs past midnight), this formula still works correctly as long as Excel recognizes both entries as valid times.

Using decimal hours in payroll calculations

Once you have converted your times to decimal, you can multiply by an hourly rate to calculate pay. Assume your decimal hours are in column B and your hourly rate is $20 in cell D1. Click on an empty cell in column C and type =B1*$D$1. The dollar signs lock the reference to D1, so when you copy the formula down, it always multiplies by the same rate.

Press Enter, then copy this formula down for all rows with time entries. Each cell in column C now shows the pay for that row. If you need to calculate overtime — for example, anything over 8 hours at time-and-a-half — you can use an IF statement: =IF(B1>8, 8*$D$1 + (B1-8)*$D$1*1.5, B1*$D$1). This pays regular rate for the first 8 hours and 1.5 times the rate for anything above that.

Fixing decimal results that show too many digits

Sometimes your decimal result shows as 2.5000000001 or 1.2499999999 instead of a clean number. This happens because of how computers store decimal values internally and does not affect your calculations — the math is still correct. To make the display cleaner, right-click on the cell and select Format Cells.

In the Format Cells dialog, click the Numbers tab. Under Category, select Number. Set the Decimal Places field to 2 (or however many decimal places you need). Click OK. The cell now displays 2.50 or 1.25, which is easier to read. The underlying value has not changed; only the display has been rounded.

Checking your conversion is correct

A quick way to verify your formula worked is to think through the math. One hour in decimal is 1.0. Thirty minutes is 0.5. Forty-five minutes is 0.75. Fifteen minutes is 0.25. If you see a result like 3.75, that is 3 hours and 45 minutes — which you can check against your original time entry. If the numbers do not match, double-check that your original cell actually contains a time format that Excel recognizes, not text that looks like a time.

If Excel is treating your time as text, the formula will return an error or zero. To fix this, you may need to re-enter the time by typing it directly into the cell, or use the TIMEVALUE function to convert text to a time first: =TIMEVALUE(A1)*24. This tells Excel to interpret the cell contents as a time before doing the multiplication.

Frequently Asked Questions

What if my time entry shows as an error after I use the formula?

The original cell probably contains text instead of an actual time value. Delete the time entry and retype it, or use =TIMEVALUE(A1)*24 to convert text to a time first. If you are copying times from another program, paste them as values only (right-click, Paste Special, Values) to avoid format conflicts.

Can I convert decimal back to time format if I change my mind?

Yes. A decimal like 2.5 can be formatted as time by right-clicking the cell, selecting Format Cells, choosing Time category, and picking a time format. It will display as 2:30. However, this only works if the decimal was created from a time value using the *24 formula; arbitrary decimals may not convert sensibly.

Why does my overnight shift calculation give the wrong answer?

If your shift runs from 11 PM to 7 AM the next day, the end time appears earlier than the start time. Excel handles this if both are entered as times on the same row, but if they are on different rows or different days, you may need to add 1 to the end time: =(B1+1-A1)*24. The +1 represents the next day.

Do I need to keep both the original time and the decimal version?

No. Once you have the decimal version, you can delete the original time column if you no longer need it. However, keeping both is useful if you want to see the time in a readable format and also use it in calculations — just put them in different columns.