Excel and Google Sheets Formulas for Decimal Time

Quick answer: Spreadsheet formulas are helpful when you need to convert dozens of timesheet entries consistently. The main decision is whether your cells contain numeric hours or actual time-of-day values.

Try it: Open the free BuzBolt Time Calculator. No signup is required.

Understand the method

If A2 contains a numeric hour count and B2 contains minutes, =A2+B2/60 returns decimal hours. If A2 holds a start clock time and B2 an end clock time, =MOD(B2-A2,1)*24 returns an elapsed decimal-hour duration for a shift shorter than 24 hours.

To deduct an unpaid break in minutes from C2, use =MOD(B2-A2,1)*24-C2/60. Ensure C2 truly contains a number of minutes, not a formatted clock time.

Worked example

With A2 = 09:00, B2 = 17:30 and C2 = 30, the result is 8.0 decimal hours. If C2 = 0, it is 8.5 hours.

What people often get wrong

MOD wraps an earlier finish into the following day; that is convenient for overnight shifts but inappropriate for durations of 24 hours or more.

A practical way to use this

Test spreadsheet formulas against two daytime examples and two overnight examples before processing an entire timesheet.

Frequently asked question

Why does a spreadsheet show a fraction instead of hours? Time values are often stored as a fraction of a day; multiplying by 24 converts that to hours.

Spreadsheet approach: store times, then calculate

If A2 contains a start time and B2 an end time, =MOD(B2-A2,1)*24 gives the elapsed duration in decimal hours when the interval is less than 24 hours. If C2 contains an unpaid break as minutes, use =MOD(B2-A2,1)*24-C2/60.

Apply it to your own task

Spreadsheet cells must genuinely be stored as times, not plain text such as “9am” that the software fails to recognise. MOD is helpful for an overnight interval, but it deliberately wraps around after 24 hours. Use dates for multi-day intervals.