TIME Function in Microsoft Excel
Part 1: Introduction
Definition
The TIME function in Microsoft Excel is a built-in function that returns the decimal number for a particular time. If the cell format was General before the function was entered, the result is formatted as a date.
Purpose
The purpose of the TIME function is to create a time with individual hour, minute, and second components. The function is useful in calculations involving times and for inputting time values.
Syntax & Arguments
The syntax of the TIME function is as follows:
TIME(hour, minute, second) Arguments:
- Hour: Required. A number from 0 (zero) to 32767 represents the hour. Any value greater than 23 will be divided by 24, and the remainder will be treated as the hour value.
- Minute: Required. A number from 0 to 32767 represents the minute. Any value greater than 59 will be converted to hours and minutes.
- Second: Required. A number from 0 to 32767 represents the second. Any value greater than 59 will be converted to hours, minutes, and seconds.
Return Value
The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).
Remarks
Time values are a portion of a date value and are represented by a decimal number (for example, 12:00 PM is defined as 0.5 because it is half of a day).
Part 2: Examples
Example 1: Calculating Shift Start Time
Purpose: To calculate the start time of a work shift based on a given hour, minute, and second.
Data tables and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Hour | Minute | Second | Start Time | Formula | Result |
| 2 | 9 | 0 | 0 | =TIME(A2,B2,C2) | 9:00 AM | |
| 3 | 8 | 30 | 0 | =TIME(A3,B3,C3) | 8:30 AM | |
| 4 | 10 | 15 | 0 | =TIME(A4,B4,C4) | 10:15 AM |
Explanation: This formula calculates the shift’s start time using the given hours, minutes, and seconds.
Example 2: Calculating Meeting Times
Purpose: To calculate the time for a meeting based on hours, minutes, and seconds.
Data tables and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Hour | Minute | Second | Meeting Time | Formula | Result |
| 2 | 14 | 30 | 0 | =TIME(A2,B2,C2) | 2:30 PM | |
| 3 | 11 | 45 | 0 | =TIME(A3,B3,C3) | 11:45 AM | |
| 4 | 16 | 0 | 0 | =TIME(A4,B4,C4) | 4:00 PM |
Explanation: This formula calculates the time for a meeting using the given hours, minutes, and seconds.
Example 3: Calculating Break Times
Purpose: To calculate the time for breaks during a workday.
Data tables and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Hour | Minute | Second | Break Time | Formula | Result |
| 2 | 12 | 15 | 0 | =TIME(A2,B2,C2) | 12:15 PM | |
| 3 | 15 | 0 | 0 | =TIME(A3,B3,C3) | 3:00 PM | |
| 4 | 10 | 30 | 0 | =TIME(A4,B4,C4) | 10:30 AM |
Explanation: This formula calculates the time for breaks using the given hours, minutes, and seconds.
Example 4: Calculating Time for Daily Reports
Purpose: To calculate the time when daily reports are due.
Data tables and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Hour | Minute | Second | Report Time | Formula | Result |
| 2 | 17 | 0 | 0 | =TIME(A2,B2,C2) | 5:00 PM | |
| 3 | 16 | 30 | 0 | =TIME(A3,B3,C3) | 4:30 PM | |
| 4 | 18 | 0 | 0 | =TIME(A4,B4,C4) | 6:00 PM |
Explanation: This formula calculates when daily reports are due using the given hours, minutes, and seconds.
Example 5: Calculating Time for Client Calls
Purpose: To calculate the time for scheduled client calls.
Data tables and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Hour | Minute | Second | Call Time | Formula | Result |
| 2 | 13 | 15 | 0 | =TIME(A2,B2,C2) | 1:15 PM | |
| 3 | 10 | 0 | 0 | =TIME(A3,B3,C3) | 10:00 AM | |
| 4 | 15 | 45 | 0 | =TIME(A4,B4,C4) | 3:45 PM |
Explanation: This formula calculates the time for scheduled client calls using the given hours, minutes, and seconds.
These examples demonstrate how the TIME function can be used in various business scenarios to calculate specific times based on given hours, minutes, and seconds.
Example 6: Using TIME with IF to Check if it’s Time for Lunch
Purpose: To check if it’s time for lunch based on the current time.
Data tables and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Hour | Minute | Second | Lunch Time? | Formula | Result |
| 2 | 12 | 0 | 0 | =IF(TIME(A2,B2,C2)=TIME(12,0,0), “Yes”, “No”) | Yes | |
| 3 | 11 | 0 | 0 | =IF(TIME(A3,B3,C3)=TIME(12,0,0), “Yes”, “No”) | No | |
| 4 | 13 | 0 | 0 | =IF(TIME(A4,B4,C4)=TIME(12,0,0), “Yes”, “No”) | No |
Explanation: This formula checks if the current time equals 12:00 PM (lunchtime). If it is, it returns “Yes”; otherwise, it replaces “No”.
Example 7: Using TIME with SUM to Calculate Total Work Hours
Purpose: To calculate total work hours based on start and end times.
Data tables and formulas:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Start Hour | Start Minute | End Hour | End Minute | Work Hours | Formula | Result |
| 2 | 9 | 0 | 17 | 0 | =SUM(TIME(C2,D2,0)-TIME(A2,B2,0)) | 8 hours | |
| 3 | 8 | 0 | 16 | 0 | =SUM(TIME(C3,D3,0)-TIME(A3,B3,0)) | 8 hours | |
| 4 | 10 | 0 | 18 | 0 | =SUM(TIME(C4,D4,0)-TIME(A4,B4,0)) | 8 hours |
Explanation: This formula calculates the total work hours by subtracting the start and end times.
Example 8: Using TIME with VLOOKUP to Find Work Shifts
Purpose: To find the work shift for a given employee based on their start time.
Data tables and formulas:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Employee | Start Hour | Start Minute | Shift | Shift Lookup | Formula | Result |
| 2 | John | 9 | 0 | Morning | =VLOOKUP(TIME(B2,C2,0),E2:F4,2,TRUE) | Morning | |
| 3 | Jane | 13 | 0 | Afternoon | =VLOOKUP(TIME(B3,C3,0),E2:F4,2,TRUE) | Afternoon | |
| 4 | Joe | 17 | 0 | Evening | =VLOOKUP(TIME(B4,C4,0),E2:F4,2,TRUE) | Evening |
Explanation: This formula uses VLOOKUP to find the work shift for a given employee based on their start time.
Example 9: Using TIME with AVERAGE to Calculate Average Meeting Time
Purpose: To calculate the average meeting time based on the start times of several meetings.
Data tables and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Meeting 1 | Meeting 2 | Meeting 3 | Average Time | Formula | Result |
| 2 | 9:00 AM | 10:00 AM | 11:00 AM | =AVERAGE(TIMEVALUE(A2), TIMEVALUE(B2), TIMEVALUE(C2)) | 10:00 AM | |
| 3 | 8:00 AM | 9:00 AM | 10:00 AM | =AVERAGE(TIMEVALUE(A3), TIMEVALUE(B3), TIMEVALUE(C3)) | 9:00 AM | |
| 4 | 7:00 AM | 8:00 AM | 9:00 AM | =AVERAGE(TIMEVALUE(A4), TIMEVALUE(B4), TIMEVALUE(C4)) | 8:00 AM |
Explanation: This formula calculates the average meeting time by converting the meeting times to decimal values, averaging them, and then converting the result back to a time.
Example 10: Using TIME with MAX to Find Latest Meeting Time
Purpose: To find the latest meeting time based on the start times of several meetings.
Data tables and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Meeting 1 | Meeting 2 | Meeting 3 | Latest Time | Formula | Result |
| 2 | 9:00 AM | 10:00 AM | 11:00 AM | =MAX(TIMEVALUE(A2), TIMEVALUE(B2), TIMEVALUE(C2)) | 11:00 AM | |
| 3 | 8:00 AM | 9:00 AM | 10:00 AM | =MAX(TIMEVALUE(A3), TIMEVALUE(B3), TIMEVALUE(C3)) | 10:00 AM | |
| 4 | 7:00 AM | 8:00 AM | 9:00 AM | =MAX(TIMEVALUE(A4), TIMEVALUE(B4), TIMEVALUE(C4)) | 9:00 AM |
Explanation: This formula finds the latest meeting time by converting the meeting times to decimal values, finding the maximum value, and then converting the result back to a time.
Example 11: Using TIME with MIN to Find the Earliest Meeting Time
Purpose: To find the earliest meeting time based on the start times of several meetings.
Data tables and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Meeting 1 | Meeting 2 | Meeting 3 | Earliest Time | Formula | Result |
| 2 | 9:00 AM | 10:00 AM | 11:00 AM | =MIN(TIMEVALUE(A2), TIMEVALUE(B2), TIMEVALUE(C2)) | 9:00 AM | |
| 3 | 8:00 AM | 9:00 AM | 10:00 AM | =MIN(TIMEVALUE(A3), TIMEVALUE(B3), TIMEVALUE(C3)) | 8:00 AM | |
| 4 | 7:00 AM | 8:00 AM | 9:00 AM | =MIN(TIMEVALUE(A4), TIMEVALUE(B4), TIMEVALUE(C4)) | 7:00 AM |
Explanation: This formula finds the earliest meeting time by converting the meeting times to decimal values, finding the minimum value, and then converting the result back to a time.
Example 12: Using TIME with COUNTIF to Count Meetings in the Morning
Purpose: To count the number of meetings that start in the morning.
Data tables and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Meeting 1 | Meeting 2 | Meeting 3 | Morning Meetings | Formula | Result |
| 2 | 9:00 AM | 10:00 AM | 11:00 AM | =COUNTIF(A2:C2, “<“&TIME(12,0,0)) | 3 | |
| 3 | 8:00 AM | 1:00 PM | 2:00 PM | =COUNTIF(A3:C3, “<“&TIME(12,0,0)) | 1 | |
| 4 | 7:00 AM | 2:00 PM | 3:00 PM | =COUNTIF(A4:C4, “<“&TIME(12,0,0)) | 1 |
Explanation: This formula counts the number of meetings that start before 12:00 PM (noon).
Example 13: Using TIME with IFERROR to Handle Errors
Purpose: To handle errors when calculating the end time of a work shift.
Data tables and formulas:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Start Hour | Start Minute | Shift Length | End Time | Formula | Result |
| 2 | 9 | 0 | 8 | =IFERROR(TIME(A2+B2, C2, 0), “Error”) | 5:00 PM | |
| 3 | 8 | 0 | 9 | =IFERROR(TIME(A3+B3, C3, 0), “Error”) | 5:00 PM | |
| 4 | 10 | 0 | 10 | =IFERROR(TIME(A4+B4, C4, 0), “Error”) | Error |
Explanation: This formula calculates the end time of a work shift. If the end time is later than 24 hours, it returns an error. The IFERROR function replaces this error with the text “Error”.
Part 3: Tips and Tricks
- Time as a decimal: Remember that Excel stores time as a fraction of a day. So, 12:00 PM is stored as 0.5, representing half of the day.
- 24-hour clock: Excel uses a 24-hour clock for calculations. So, if you enter a time without specifying AM or PM, Excel will assume you are using a 24-hour clock.
- Negative time values: If your calculation results in a negative time value, Excel will display a series of hash marks (####). To display negative time values, you must change the date system in Excel to the 1904 date system.
- Formatting time: You can format cells to display time in various