How to Calculate Time in Excel: Shifts, Durations, and Totals

To calculate elapsed time in Excel, subtract the start from the end. If A2 contains the start and B2 contains the end, enter =B2-A2. A shift from 9:15 AM to 5:45 PM produces a numeric duration that can display as 8:30, convert to 8.5 total hours, or become text for a report.

Choose the result you need before choosing the format. Excel stores valid time and date-time values as numbers. Subtraction returns a fraction of a day, while formatting changes how that value appears. Microsoft documents this approach in its guidance for calculating the difference between two times.

Use full date-time values whenever dates affect the interval. A time-only value cannot tell Excel whether an end time belongs to the same day or the following day without an explicit rule.

The short answer: subtract the start from the end

Put the start in A2 and the end in B2, then enter:

=B2-A2

Format the result as h:mm for an ordinary duration under 24 hours. If the result will be totaled across rows and may exceed 24 hours, use [h]:mm. If you need a numeric total in hours, multiply the duration by 24 instead.

For valid date-time values, the same formula works across days. A start of November 8 at 10:00 PM and an end of November 9 at 6:00 AM returns an eight-hour duration when both cells contain the actual dates and times.

Choose the right result: duration, decimal hours, or display text

For a shift from 9:15 AM to 5:45 PM, the elapsed time is eight hours and 30 minutes. These formulas produce different types of output:

• Numeric duration: =B2-A2, formatted as h:mm, displays 8:30. Keep this value for sums, comparisons, and later calculations.
• Decimal hours: =(B2-A2)*24 returns the numeric value 8.5. Format the result as Number with the required decimal places.
• Presentation text: =TEXT(B2-A2,"h:mm") displays 8:30 as text. Use it only at the presentation boundary.

Microsoft’s TEXT function reference explains how numeric values become formatted text. A TEXT result is not a numeric duration for later arithmetic.

Calculate an overnight shift or a multi-day interval

The reliable approach is to record the actual date and time in both cells, then subtract the start from the end. This handles overnight work and intervals spanning multiple days without relying on an assumption about midnight.

If you have time-only values and the approved rule is that an end earlier than the start means the shift ended the next day, use this bounded pattern:

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

This formula assumes at most one midnight crossing. It is not a universal rule for ambiguous timestamps. If the dates are known, or the work can last more than one day, store full date-time values instead.

To leave the result blank until both entries exist, use:

=IF(OR(A2="",B2=""),"",B2-A2)

This blank check controls the display only. It does not validate the source cells, so the entries still need to be valid Excel-recognized values.

When a time is stored as text, TIMEVALUE can convert text representing a time into an Excel time value. It ignores date context, so do not use it alone for an interval that may span days.

Sum time without losing hours after 24

Suppose two numeric durations are 12:45 and 15:30. Their sum is 28:15. Use either of these formulas:

=SUM(B2:B3)
=B2+B3

Apply the custom format [h]:mm to the result. Square brackets tell Excel to display cumulative hours instead of resetting the hour count after 24.

With ordinary h:mm, the same numeric total can appear as 4:15 because that format uses a 24-hour clock cycle. In desktop Excel, select the result cell, open Format Cells, choose Custom, and enter [h]:mm. Microsoft states that Excel for the web can calculate totals over 24 hours but does not apply custom number formats in the same way as desktop Excel. See Microsoft’s instructions for adding and subtracting time.

Calculate total hours, minutes, and seconds

Because Excel stores a duration as a fraction of a day, multiply the numeric duration by the number of units in a day when you need cumulative totals:

• =(B2-A2)*24 returns total decimal hours.
• =(B2-A2)*1440 returns total minutes.
• =(B2-A2)*86400 returns total seconds.
• =INT((B2-A2)*24) returns complete whole hours and discards the fractional hour.

HOUR, MINUTE, and SECOND return components of a time value, not unrestricted cumulative totals. For example, HOUR is not a substitute for multiplying by 24 when the interval may exceed one day.

Similarly, TIMEVALUE is useful for converting text that represents a clock time, but it discards date information. Use full date-time values when the elapsed period depends on the date.

Calculate billable hours without changing source data

Keep the source timestamps, calculated duration, hourly rate, and cost in separate fields. If A2 is the start date-time, B2 is the end date-time, and C2 is the hourly rate, calculate decimal hours with:

=(B2-A2)*24

Then calculate cost by multiplying the numeric hours by the rate:

=((B2-A2)*24)*C2

For the illustrative interval from 9:15 AM to 5:45 PM at 40 per hour, the result is 8.5 hours and 340 in the rate’s currency. Before treating that amount as billable, define break deductions, rounding, currency, and the rate’s effective period. A missing timestamp or rate should go to a person for resolution, not silently become zero. This is an arithmetic example, not payroll, invoicing, or tax advice.

When Excel stops being the right timesheet workflow

Excel is practical for a small, transparent calculation. Consider a managed timesheet process when several people enter time, approvals matter, billable labels are needed, or it is difficult to identify who owns corrections.

ClickUp documents task time tracking, a Timesheets Hub for viewing and filtering tracked time, and a timesheet approval workflow that includes submission, locking during review, approval, requests for changes, and reopening. Feature availability varies by plan and workspace. These documented interface capabilities do not establish an API, export route, or CRM synchronization workflow.

For a human-reviewed process, the submitter records time against the relevant task, submits the timesheet for the period, and an authorized approver reviews it. If changes are requested, the submitter corrects the entry through the documented review process. Keep individual time entries distinct from task totals and user-period timesheets. An aggregate is not a raw time-entry record.

Teams evaluating a managed setup can review ClickUp setup and automations. Confirm current plan access, permissions, and any required data-transfer method before implementation.

Use the correct record grain for reporting

A time-entry workbook and a reporting workflow answer different questions. Define the record before building totals:

• Individual time entry: one row for one tracked entry, including its source identifier, task, user, duration, and approval state.
• Task-day aggregate: one row for a task, user, and reporting date. It is a summary, not a replacement for the source entries.
• Timesheet-period record: one row for a user and approval period, with its submission and approval state.
• Dashboard aggregate: a calculated total grouped by fields such as task, user, team, or date range.

If a future system transfers individual entries, use the source system’s immutable time-entry identifier as the proposed uniqueness key. A task ID plus date is not universal because multiple entries can exist for the same task and date. Where concurrent processes can write records, use a database-enforced unique constraint or transactional upsert rather than relying on a lookup-then-create sequence. This is an illustrative implementation pattern, not a documented ClickUp integration contract.

Check the workbook before trusting a time total

If a result is negative, blank, or stale, identify whether the issue is in the inputs, date context, display format, blank-entry rule, or calculation settings before changing the formula.

For formulas involving NOW(), Excel recalculates according to workbook settings. If the value looks stale, check whether calculation is Automatic or Manual and use the applicable recalculation command. Microsoft explains these options in its calculation settings guidance.

In short, subtract start from end for a numeric duration, use [h]:mm for cumulative hours, and multiply by 24 when the required result is a numeric total in hours.