Spreadsheets like Microsoft Excel and Google Sheets are incredibly powerful tools for tracking project timelines, employee schedules, and financial records. However, performing time calculations in these programs can often be frustrating. You might encounter formatting errors, totals that reset after reaching 24 hours, or cell grids filled with ####### errors.

These issues occur because spreadsheets store time values differently than standard text or decimal numbers. Once you understand how these systems work, setting up time calculations becomes much easier. This guide shows you the formulas and settings needed to calculate elapsed times in Excel and Google Sheets.

1. How Excel and Google Sheets Store Time

To write accurate formulas, you must first understand how spreadsheets process dates and times internally:

• Dates are stored as whole numbers starting on January 1, 1900 (which is stored as 1).
• Times are stored as decimal fractions of a 24-hour day.

For example:

• 12 hours (half a day) is stored as 0.5.
• 6 hours (a quarter of a day) is stored as 0.25.
• 18 hours (three-quarters of a day) is stored as 0.75.

Because of this system, standard addition and subtraction work well for simple time calculations, but you must format the cells correctly to see the results in standard hours and minutes.

2. Basic Subtraction and Formatting

Let us set up a basic time card sheet:

• Start Time (Cell A2): 09:00 AM
• End Time (Cell B2): 05:30 PM
• Elapsed Duration (Cell C2): Write the formula =B2-A2

To display the result correctly:

1. Select cell C2.
2. Open the formatting options (Format > Number > Custom).
3. Set the format to hh:mm or h:mm.
4. The cell will display 8:30 (8 hours and 30 minutes).

3. Resolving Midnight Crossovers with the MOD Function

If you are tracking overnight shifts, standard subtraction will fail. For example, if an employee clocks in at 10:00 PM (A2) and clocks out at 06:00 AM (B2) on the next day, the formula =B2-A2 results in a negative number, which Excel displays as a series of hashes (#######).

You can easily handle this by using the MOD function in your formula:

=MOD(B2-A2, 1)

How This Formula Works:

• The MOD function calculates the remainder after dividing a number by a divisor.
• In spreadsheets, date-time units use 1 as a full day.
• If the difference B2-A2 is positive (like 0.5), MOD(0.5, 1) is 0.5 (8 hours).
• If the difference is negative (like -0.6), the function wraps the value and returns 0.4 (9.6 hours), correctly adjusting for midnight crossovers without needing manual date inputs.

4. Calculating Cumulative Durations Beyond 24 Hours

If you sum multiple daily durations to find total weekly hours, your total might reset to zero once it exceeds 24. For example, if you add 16 hours and 12 hours, the cell might display 4:00 instead of 28:00.

To fix this formatting issue:

1. Select your totals cell (e.g., C7).
2. Go to Format > Number > Custom.
3. Enter the custom format code [h]:mm (with square brackets around the h).
4. The spreadsheet will now display the cumulative total correctly as 28:00.

5. Converting Spreadsheet Times to Decimal Formats

To multiply total hours by an hourly wage rate, you must first convert the spreadsheet's internal time format back into standard decimal hours.

Since spreadsheets store 24 hours as 1, you can convert a time cell to decimal hours by multiplying its value by 24:

=C2 * 24

Ensure you format the result cell as a Standard Number or Decimal, rather than a time value, to see the correct decimal total.

Using these formulas and formatting rules will help you build reliable schedules and timesheets in your spreadsheets. If you want to quickly double-check your calculations, you can use our online Time Duration Calculator to verify your totals and keep your records accurate.