Time calculations in Excel confused me for a long time. I would type “9:30” into a cell expecting Excel to treat it as a time value, and sometimes it did, but other times my formulas would break because the underlying value wasn’t what I assumed. Once I learned how the TIME function actually works, everything about scheduling, timesheets, and duration calculations became far more reliable.
This article walks through exactly how the TIME function works, how Excel stores time internally, and how to use it correctly in real spreadsheets.
What Is the TIME Function in Excel?
The TIME function constructs a proper time value from three separate components: hours, minutes, and seconds. Instead of typing a time as plain text, TIME builds it mathematically, which means the result behaves like a real time value that you can add, subtract, and format however you like.
Understanding this matters because Excel stores time as a fraction of a 24-hour day. For example, 12:00 PM (noon) is internally stored as 0.5, because it’s exactly halfway through the day. The TIME function handles this conversion for you automatically.
Required Data
To use TIME, you need three numeric inputs:
- An hour value (0–23, though Excel will adjust values outside this range)
- A minute value (0–59, with automatic rollover for higher numbers)
- A second value (0–59, with automatic rollover as well)
These can be typed directly as numbers or referenced from other cells.
Basic Syntax
=TIME(hour, minute, second)
All three arguments are required. If you want a time without seconds, you still need to include 0 as the third argument.
Step-by-Step Instructions
- Click on the cell where you want the time value to appear.
- Type
=TIME(. - Enter the hour value, followed by a comma.
- Enter the minute value, followed by a comma.
- Enter the second value.
- Close the parenthesis and press Enter.
Example:
=TIME(9, 30, 0)
This creates a time value representing 9:30:00 AM.
Practical Examples
Example 1: Creating a Simple Time Value
=TIME(14, 45, 0)
This returns 2:45 PM, since Excel uses a 24-hour internal system.
Example 2: Combining TIME With Cell References
If A2 contains an hour, B2 contains minutes, and C2 contains seconds:
=TIME(A2, B2, C2)
This is useful when your data is already split into separate hour, minute, and second columns, such as from a time-tracking export.
Example 3: Adding Time to an Existing Time Value
=A2 + TIME(1, 30, 0)
This adds one hour and thirty minutes to whatever time value is in A2 — useful for calculating shift end times or appointment slots.
Example 4: Calculating Duration Between Two Times
=B2 - A2
If A2 contains a start time and B2 contains an end time, subtracting them gives you the duration. You’ll need to format the result cell as [h]:mm to display durations longer than 24 hours correctly.
Example 5: Rounding Minutes Using TIME
=TIME(HOUR(A2), MROUND(MINUTE(A2), 15), 0)
This rounds a time in A2 to the nearest 15-minute interval, which is handy for scheduling systems that only allow bookings at quarter-hour marks.
Example 6: Converting Text Time Into a Real Time Value
If a cell contains text like “9.30” that isn’t recognized as a time, you can rebuild it using TIME combined with text functions, or more directly use TIMEVALUE:
=TIMEVALUE("9:30 AM")
Common Mistakes to Avoid
- Typing times as plain text — Entering “9:30” in a cell sometimes gets auto-recognized as a time, but inconsistent formatting (like “9.30” or “930”) won’t be recognized, leading to formula errors down the line.
- Forgetting to format the result cell — TIME returns a decimal value under the hood; if the cell isn’t formatted as time, you’ll see a decimal number like 0.395833 instead of a readable time.
- Exceeding 24 hours without proper formatting — If you’re summing hours worked across multiple days and the total exceeds 24 hours, standard time formatting will wrap around instead of accumulating. Use the custom format [h]:mm to fix this.
- Mixing up hour and minute order — The arguments must go hour, then minute, then second — reversing them produces a completely different, often nonsensical, time.
- Using TIME when you actually need DATE and TIME combined — TIME only handles the time-of-day portion; if you need a full date and time stamp, you’ll need to combine it with the DATE function or use a proper datetime value.
Troubleshooting Tips
- Result looks like a decimal instead of a time: Right-click the cell, choose Format Cells, and select a Time format.
- Negative time values show as errors (#####): This happens when subtracting a later time from an earlier one results in a negative number, which Excel’s default date system can’t display. Switching to the 1904 date system (File > Options > Advanced) can resolve this, though it’s usually simpler to restructure your calculation.
- Time doesn’t add up correctly across days: Apply the [h]:mm custom number format to any cell summing durations that might exceed 24 hours.
- TIME function returns unexpected values with numbers outside normal ranges: Excel automatically rolls over excess values — for example, TIME(0, 90, 0) becomes 1:30 AM because 90 minutes equals 1 hour and 30 minutes. This is expected behavior, not an error.
Real-World Use Cases
- HR and payroll teams calculating employee clock-in and clock-out durations.
- Project managers tracking how long tasks take to complete.
- Event planners building schedules with specific start and end times.
- Call centers measuring average call handling time.
- Fitness and sports tracking recording lap times or workout durations.
- Appointment-based businesses like clinics or salons scheduling time slots.
Best Practices
- Always format cells appropriately after using TIME so the output is human-readable rather than a raw decimal.
- Use [h]:mm formatting whenever you’re summing durations that could exceed 24 hours, such as total weekly hours worked.
- Combine TIME with HOUR, MINUTE, and SECOND functions when you need to extract or rebuild specific components of an existing time value.
- Keep your source data consistent — if times are being imported from another system, verify they’re recognized as actual time values and not text before running calculations.
- When building schedules, use TIME with cell references rather than hardcoded numbers so your spreadsheet stays flexible and easy to update.
Frequently Asked Questions
What’s the difference between TIME and TIMEVALUE? TIME builds a time value from three separate numbers (hour, minute, second). TIMEVALUE converts an existing text string that looks like a time, such as “9:30 AM,” into a proper time value. Use TIME when you’re constructing a time from parts; use TIMEVALUE when you’re converting text.
Can TIME handle values over 24 hours? TIME itself always returns a value representing a portion of a single day, so it can’t directly represent something like “30 hours.” For durations exceeding 24 hours, you typically add time values together and format the result cell with the custom format [h]:mm, which allows hours to accumulate past 24 instead of resetting.
Why does subtracting two times sometimes show a negative or error result? This happens when the earlier time is subtracted from the later one in the wrong order, or when a shift crosses midnight. If your end time is technically “before” your start time on the same date, you may need to add 1 (representing a full day) to the calculation to account for the day rollover.
How do I add a specific number of minutes to a time value? You can use TIME directly, like =A2 + TIME(0, 15, 0) to add 15 minutes, or divide minutes by 1440 (the number of minutes in a day), like =A2 + 15/1440, which achieves the same result.
Does TIME work the same way in Google Sheets? Yes, Google Sheets uses an almost identical TIME function with the same argument order and underlying serial-number logic, so formulas built in Excel typically transfer over with little to no modification.
Final Thoughts
The TIME function might seem like a small, narrow tool at first, but it solves a problem that trips up a huge number of Excel users: getting time values to behave predictably in calculations. Once you understand that Excel treats time as a fraction of a day, and that TIME is simply a clean way to build that fraction from hours, minutes, and seconds, time-based formulas stop feeling mysterious and start becoming one of the more reliable parts of your spreadsheet toolkit.
