📎 This article includes 1 downloadable practice file ↓
In this article
TIMEVALUE turns text like “2:45 PM” into a real time you can add and subtract.
Syntax
=TIMEVALUE(time_text)| Argument | What it means |
|---|---|
time_text |
Text Excel recognises as a time. |
Examples
Example 1
=TIMEVALUE(C2)-TIMEVALUE(B2)Shift length from two text columns; format as [h]:mm.
Common errors and fixes
| You see | Why, and the fix |
|---|---|
#VALUE! |
Text like “14.45” or “2:45PM IST” — clean it with SUBSTITUTE first. |
TIMEVALUE in practice
Where you will use it
- Convert text times from exports
- Calculate durations from text
- Sort times stored as text
Worked example
| Text time | Formula | Result |
|---|---|---|
| 9:45 AM | =TEXT(TIMEVALUE(A2),"hh:mm:ss") | 09:45:00 |
| 18:30 | =TEXT(TIMEVALUE(A3),"hh:mm:ss") | 18:30:00 |
| 11:59:59 PM | =TEXT(TIMEVALUE(A4),"hh:mm:ss") | 23:59:59 |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Text with dates | Date part is dropped |
| Bad separators | Use : between parts |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
⚡ Interactive Practice Lab & Formula Tester:
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Dates & times22 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 34 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong