The time function is used to return the time in number between 0 and 23 from a given time.
Hour function: a brief
The time function in Excel Extract the time from a given time and returns a Number between 0 and 23. For example, if the time is 10:30 am, the HOUR function will come back 10. You can use this function to return the value of the time in a cell.
|
Objective |
Value returned by function |
|---|---|
|
Aims to obtain the Time value as a number |
The HOUR function will return a numerical value of the value of the input time. |
Hour function: a syntax:
=HOUR(hour_value)
- HOUR_VALUE: It should be a valid Excel hour value.
Important notes on the hours function:
- The time function in Excel Extract the value of the time of a given time and returns a Number between 0 and 23.
- For example, if the time is 11:00The time function will return numerical value as 11. It requires only one argument,, serial_numbervalid Excel Date Or Excel the time.
- You can enter time values like text (for example, “9:25 pm”) or as decimal figures (For example, 0.5equivalent 12:00). The time function returns the equivalent numerical value.
- In the image above, the input value is 0.34 Decimal value But the time function turns into a Excel the time and returns the Numerical value equivalent at the entrance time value. The time function therefore considers the decimal value as 08:09 am and returns the numerical value like 8 in the output cell.
|
Decimal values |
Equivalent hour value in Excel |
|---|---|
|
0.01 |
12 h 14 |
|
0.02 |
12:29 p.m. |
|
0.03 |
12:43 p.m. |
|
0.04 |
12:57 p.m. |
|
0.05 |
1:12 |
|
0.06 |
1:26 am |
|
0.07 |
1h41 |
|
0.08 |
1h55 |
|
0.09 |
2h10 |
|
0.10 |
2h24 |
|
0.125 |
3h00 |
|
0.15 |
3:36 am |
|
0.1667 |
4:00 |
|
0.20 |
4:48 |
|
0.25 |
6:00 |
|
0.30 |
7:12 am |
|
0.3333 |
8:00 am |
|
0.375 |
9:00 am |
|
0.40 |
9:36 am |
|
0.50 |
12:00 p.m. (noon) |
|
0.55 |
1:12 p.m. |
|
0.5833 |
2 p.m. |
|
0.60 |
2:24 p.m. |
|
0.625 |
3 p.m. |
|
0.6667 |
4:00 p.m. |
|
0.70 |
4:48 p.m. |
|
0.75 |
6:00 p.m. |
|
0.80 |
7:12 p.m. |
|
0.8333 |
8 p.m. |
|
0.875 |
9 p.m. |
|
0.90 |
9:36 p.m. |
|
0.9167 |
10 p.m. |
|
0.95 |
11:24 p.m. |
|
1.00 |
12:00 (midnight) |
From the table above, you can find the decimal values and the equivalent time values. The decimal value must be lies between 0 to 1.
Example 1: Basic example:
The example below will easily make you understand the time function.
|
Entry time |
Formula used |
Final result |
Comments |
|---|---|---|---|
|
10:45 am |
= Time (B3) |
10 |
Returns 10 because the time is 10 |
|
5.30 p.m. |
= Time (B4) |
17 |
Return 5 p.m. to 5 p.m. is represented 17 in a 24 -hour format |
|
00:15 |
= Time (B5) |
0 |
Return 0, indicating midnight |
|
23:59 |
= Time (B6) |
23 |
Returns 23, indicating 11:59 p.m. |
|
12:00 |
= Time (B7) |
12 |
Back 12 |
Example 2: return time values from date and time values:
If your leaf contains both the date and time values in the cells, you can use the time function to extract the Digital values by ignoring the date values of the input cell.
|
Date and time of entry |
Formula used |
Final result |
Comments |
|---|---|---|---|
|
03-08-2025 10:30 |
= Time (B3) |
10 |
Returns 10 because the time is 10:30 am. |
|
25-12-2024 00:01 |
= Time (B4) |
Returns 0, because midnight is 0. |
Notes:
- If the time_Value is not recognized by the hour function then it will send the #VALUE! error.
- If the time_Value is out of reach given, then the time function returns the #Num! error.
That's it. Is this tutorial initially published on the excellent Excel function function?
