Convert Time to Decimal in Excel & Google Sheets

Why Multiply by 24?

Excel and Google Sheets record one day as the number 1.0, so one hour is 1/24 and one minute is 1/1440. The value 7:45 is stored as 7.75 ÷ 24 ≈ 0.3229. Multiplying by 24 reverses that and gives 7.75 decimal hours — exactly what payroll needs.

Using HOUR and MINUTE Instead

If you prefer an explicit formula, extract the parts and divide minutes by 60:

=HOUR(A1) + MINUTE(A1)/60 + SECOND(A1)/3600

For 7:45 this returns 7 + 45/60 + 0 = 7.75. The multiplier method (A1*24) is shorter and works in every version, so it is usually the better choice.

Text Times Like “7:45”

If your time is text, wrap it with TIMEVALUE:

=TIMEVALUE("7:45")*24

This returns 7.75. If hours can exceed 24 (e.g. a weekly total like 45:30 entered as text), split the text on the colon:

=LEFT(A1,FIND(":",A1)-1) + MID(A1,FIND(":",A1)+1,2)/60

Calculate Pay from Time in a Spreadsheet

Once time is in decimal hours, multiply by the rate. With clock-in in A1, clock-out in B1, lunch in C1 (minutes) and an hourly rate of $20:

=((B1-A1)*24 - C1/60) * 20

For a 9:00–17:00 shift with a 60-minute lunch this gives (8 − 1) × 20 = $140.00. For a no-formula version, total the week in the time card calculator, or convert single values with the time to decimal calculator.

Common Errors

• Result shows as a time, not a number. Format the cell as Number/General — the value is already correct.
• Result over 24 resets to zero. Apply a number format, not a time format, to totals.
• AM/PM mismatch. Use 24-hour entries or ensure cells are recognized as time, not as text.

For a printable reference of every minute value, see the minutes to decimal chart; for payroll rounding rules see payroll time conversion; and a plain-language explanation is in how to calculate decimal time.

Filling a Whole Column at Once

Once the first row has a working formula, grab the fill handle (the small square at the cell's bottom-right) and drag down. Excel and Google Sheets copy the formula to every row, adjusting the references automatically. A 200-row timesheet converts in one motion instead of 200 copy-pastes.

12-Hour vs 24-Hour Time Formats

Spreadsheets store time as a fraction of a day, so both "9:30 AM" and "17:30" work — but only if the cell is a real time format, not text. If your values are typed as "9:30 AM" and the formula returns 0 or an error, re-format the column as Time first. The multiply-by-24 trick then works on every row.

Summing a Column of Decimal Hours

After conversion, total the decimal column with a normal =SUM(B2:B50). The result is already in decimal hours, so it drops straight into a pay formula: =SUM(B2:B50)*rate. No extra conversion step is needed because the column is pure numbers.

Building a Pay Column

Add a column next to decimal hours with =B2*20 (for a $20 rate) and fill down. Each row shows that day's pay; the column total is gross wages. This is the spreadsheet equivalent of the time card calculator, and it scales to any number of rows.

Fixing the #VALUE! Error

• The cell is text, not time — convert the format to Time, or use the text-parsing formulas from the section above.
• A stray space or non-breaking character in the cell breaks parsing — retype the value or use TRIM.
• An empty cell in a range returns 0, not an error — that is normal, not a bug.

Excel vs Google Sheets: What Changes

The multiply-by-24 method works identically in both, but a few details differ. Google Sheets handles time cells the same way, yet some Excel keyboard shortcuts (like the fill handle behaviour) look different. Array formulas (below) use the same syntax in both. If a formula works in one, it works in the other — the conversion logic is identical.

Automating With Array Formulas

Instead of filling down row by row, use a single array formula that converts a whole column at once:

=ARRAYFORMULA((B2:B50-INT(B2:B50))*24)

This returns decimal hours for every time in B2:B50 in one cell. In modern Excel, =MAP() or a table column calculation does the same. One formula, no dragging, and new rows are converted automatically when the range is a table.

A Reusable Template Layout

Build the sheet once and reuse it forever:

• Column A — date
• Column B — clock-in (Time format)
• Column C — clock-out (Time format)
• Column D — lunch (Time or decimal)
• Column E — decimal hours: =(C2-B2-D2)*24
• Column F — pay: =E2*rate

Total E with =SUM(E:E) and you have the week's decimal hours and gross pay in two cells. Save it as a template and every timesheet is a copy-paste of raw times.

Excel Shortcuts That Save Time

• Fill handle — drag the formula down instead of copying row by row.
• Format Painter — apply the Time format to a whole column in one click.
• Table (Ctrl+T) — new rows auto-fill the decimal formula, no dragging.
• Named ranges — name the rate cell so formulas read =E2*Rate.

Handling Zero and Unexpected Values

An empty time cell returns 0 in the decimal column — that is fine, not an error. A text value returns #VALUE!; re-format it as Time or parse it. A negative result (out before in, no date) means the cell is text or the times crossed midnight without a date — add the date or use a 24-hour value.

A Real Example File Walkthrough

Imagine B2 = 09:00, C2 = 17:30, D2 = 0:30. The decimal formula =(C2-B2-D2)*24 gives 8.00. Fill down for the week; =SUM(E:E) returns 39.00; =E:E*Rate (or a SUM of the pay column) returns gross pay. The entire week is three formulas and a rate — no manual conversion anywhere.

Excel Tips for Large Timesheets

For sheets with hundreds of rows, convert the whole column with one array formula and total it with =SUM — no per-row dragging. Freeze the header row so labels stay visible while scrolling, and use a table so new rows inherit the formula automatically. These small setup steps turn a daunting monthly sheet into a two-minute task.

Wrap-Up

Excel and Google Sheets turn clock times into decimal hours with one multiply-by-24 formula — filled down a column, arrayed across a range, or built into a reusable template. Pair the spreadsheet with the converter on this site for the rare value that will not parse, and every timesheet becomes a copy-paste of raw times plus three formulas.

You're Set

With the multiply-by-24 formula and a reusable template, time-to-decimal in Excel or Sheets takes minutes. Paste your raw clock times and let the spreadsheet do the rest.