Building a Time Tracking Sheet in Excel: Dates, Times, and Calculations

How Excel Stores Time

Excel stores time as a decimal fraction of a day. 12:00 PM = 0.5, 6:00 PM = 0.75, 6:00 AM = 0.25.

Date + time together: January 15, 2025 at 2:30 PM is stored as 45672.604167 (the integer is the date, the decimal is the time).

This means you can add and subtract times mathematically, and the result makes sense.

Basic Time Sheet Structure

A typical layout:

• Column A: Date
• Column B: Start time
• Column C: End time
• Column D: Break (in minutes or hours)
• Column E: Total hours = (C - B) - D

Format Column E as [h]:mm to show hours and minutes, even when total exceeds 24 hours.

Handling Overnight Shifts

If someone works from 10 PM to 6 AM, simple subtraction gives a negative number. Fix with:

=IF(C2<B2, C2+1-B2, C2-B2)

This adds 1 (a full day) when end time is less than start time, correctly calculating overnight hours.

Converting Hours to Decimal

Payroll often needs decimal hours (7.5 instead of 7:30). Multiply by 24:

=E2*24

This converts Excel's time fraction to actual hours.

Weekly Totals with SUMIF

Sum hours for a specific week using the date column:

=SUMIFS(E:E, A:A, ">="&week_start, A:A, "<="&week_end)*24

Multiply by 24 to get decimal hours.

Overtime Calculations

Calculate hours over 40 per week:

=MAX(0, weekly_total - 40)

Regular hours: =MIN(weekly_total, 40)

Official Resources

• Calculate time differences — Microsoft time guide
• NETWORKDAYS function — business days calculation