Function: TIMEVALUE()
🔍 Purpose:
The TIMEVALUE() function converts a text representation of time (like "6:30 PM") into an actual Excel time value that Excel can recognize and use in calculations.
It’s especially useful when time data is imported from another system or typed manually as text — because Excel treats text and real time values differently.
🧮 Syntax: =TIMEVALUE(time_text)
Parameters:
-
time_text → A time written in text format (for example,
"2:45 PM"or"14:30:00").
📘 Explanation:
Excel internally stores time as a fraction of a 24-hour day,
-
6:00 AM = 0.25
-
12:00 PM = 0.5
-
6:00 PM = 0.75
But when you import or type "6:00 PM" as text, Excel doesn’t treat it as a time value — it’s just a string.
Using TIMEVALUE(), you can convert that string into a real numeric time Excel can understand for formulas or time calculations.
Example 1: Attendance Text to Time Conversion
| Employee Name | In Time (Text) | Formula | In Time (Value) |
|---|---|---|---|
| Rakesh Sharma | “9:15 AM” | =TIMEVALUE(B2) |
9:15 AM |
| Priya Roy | “8:45:30 AM” | =TIMEVALUE(B3) |
8:45:30 AM |
| Arjun Patel | “10:00 PM” | =TIMEVALUE(B4) |
10:00 PM |
| Neha Gupta | “7:25:45 AM” | =TIMEVALUE(B5) |
7:25:45 AM |
💡 Tip: Format the result column as Time (Right-click → Format Cells → Time).
This is commonly used in attendance sheets where imported times appear as text due to CSV import.
Example 2: Transport Arrival Logs
| Vehicle No | Arrival Time (Text) | Formula | Arrival Time (Value) |
|---|---|---|---|
| WB23A4451 | “06:45 AM” | =TIMEVALUE(B2) |
6:45 AM |
| MH12K9910 | “08:10:30 AM” | =TIMEVALUE(B3) |
8:10:30 AM |
| DL10B5567 | “11:25 PM” | =TIMEVALUE(B4) |
11:25 PM |
| GJ01F2333 | “02:15:00 PM” | =TIMEVALUE(B5) |
2:15 PM |
🚚 Logistics companies and Indian courier agencies often use this when importing timing data from tracking software.
⚙️ Real-Life Uses:
-
Converting text-formatted timestamps into real Excel times for calculations.
-
Cleaning up imported CSV data from biometric or GPS devices.
-
Preparing accurate time reports for transport or attendance analysis.
-
Calculating time differences (e.g., shift duration or travel time) after conversion.
🧩 Project Tasks for Students
🧠 Project Task 1: Hospital Shift Timing
You work in a private hospital in Delhi. Nurse shift data is imported as text.
Convert it into real time using TIMEVALUE().
| Nurse Name | Shift Start (Text) | Shift End (Text) | Start (Value) | End (Value) |
|---|---|---|---|---|
| Ananya Paul | “7:30 AM” | “2:30 PM” | ||
| Kavita Singh | “2:15 PM” | “9:15 PM” | ||
| Ritu Das | “9:00 PM” | “6:00 AM” | ||
| Arti Jain | “6:15 AM” | “1:45 PM” |
👉 Use =TIMEVALUE(B2) and =TIMEVALUE(C2)
Then calculate total working hours as:=(End - Start)*24
🧩 Project Task 2: Indian Airlines Flight Schedule Conversion
Flight arrival and departure times are imported from a web portal as text.
Convert them into usable time format for duration calculation.
| Flight No | Departure (Text) | Arrival (Text) | Departure (Value) | Arrival (Value) |
|---|---|---|---|---|
| AI301 | “5:30 AM” | “8:45 AM” | ||
| SG456 | “10:15 AM” | “1:25 PM” | ||
| 6E789 | “3:00 PM” | “5:55 PM” | ||
| UK112 | “8:10 PM” | “11:45 PM” |
👉 Convert with TIMEVALUE() and calculate flight duration:=(Arrival - Departure)*24 → in hours