# 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:

syntax
`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:

ABCDEF
1HourMinuteSecondStart TimeFormulaResult
2900=TIME(A2,B2,C2)9:00 AM
38300=TIME(A3,B3,C3)8:30 AM
410150=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:

ABCDEF
1HourMinuteSecondMeeting TimeFormulaResult
214300=TIME(A2,B2,C2)2:30 PM
311450=TIME(A3,B3,C3)11:45 AM
41600=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:

ABCDEF
1HourMinuteSecondBreak TimeFormulaResult
212150=TIME(A2,B2,C2)12:15 PM
31500=TIME(A3,B3,C3)3:00 PM
410300=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:

ABCDEF
1HourMinuteSecondReport TimeFormulaResult
21700=TIME(A2,B2,C2)5:00 PM
316300=TIME(A3,B3,C3)4:30 PM
41800=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:

ABCDEF
1HourMinuteSecondCall TimeFormulaResult
213150=TIME(A2,B2,C2)1:15 PM
31000=TIME(A3,B3,C3)10:00 AM
415450=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:

ABCDEF
1HourMinuteSecondLunch Time?FormulaResult
21200=IF(TIME(A2,B2,C2)=TIME(12,0,0), “Yes”, “No”)Yes
31100=IF(TIME(A3,B3,C3)=TIME(12,0,0), “Yes”, “No”)No
41300=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:

ABCDEFG
1Start HourStart MinuteEnd HourEnd MinuteWork HoursFormulaResult
290170=SUM(TIME(C2,D2,0)-TIME(A2,B2,0))8 hours
380160=SUM(TIME(C3,D3,0)-TIME(A3,B3,0))8 hours
4100180=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:

ABCDEFG
1EmployeeStart HourStart MinuteShiftShift LookupFormulaResult
2John90Morning=VLOOKUP(TIME(B2,C2,0),E2:F4,2,TRUE)Morning
3Jane130Afternoon=VLOOKUP(TIME(B3,C3,0),E2:F4,2,TRUE)Afternoon
4Joe170Evening=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:

ABCDEF
1Meeting 1Meeting 2Meeting 3Average TimeFormulaResult
29:00 AM10:00 AM11:00 AM=AVERAGE(TIMEVALUE(A2), TIMEVALUE(B2), TIMEVALUE(C2))10:00 AM
38:00 AM9:00 AM10:00 AM=AVERAGE(TIMEVALUE(A3), TIMEVALUE(B3), TIMEVALUE(C3))9:00 AM
47:00 AM8:00 AM9: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:

ABCDEF
1Meeting 1Meeting 2Meeting 3Latest TimeFormulaResult
29:00 AM10:00 AM11:00 AM=MAX(TIMEVALUE(A2), TIMEVALUE(B2), TIMEVALUE(C2))11:00 AM
38:00 AM9:00 AM10:00 AM=MAX(TIMEVALUE(A3), TIMEVALUE(B3), TIMEVALUE(C3))10:00 AM
47:00 AM8:00 AM9: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:

ABCDEF
1Meeting 1Meeting 2Meeting 3Earliest TimeFormulaResult
29:00 AM10:00 AM11:00 AM=MIN(TIMEVALUE(A2), TIMEVALUE(B2), TIMEVALUE(C2))9:00 AM
38:00 AM9:00 AM10:00 AM=MIN(TIMEVALUE(A3), TIMEVALUE(B3), TIMEVALUE(C3))8:00 AM
47:00 AM8:00 AM9: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:

ABCDEF
1Meeting 1Meeting 2Meeting 3Morning MeetingsFormulaResult
29:00 AM10:00 AM11:00 AM=COUNTIF(A2:C2, “<“&TIME(12,0,0))3
38:00 AM1:00 PM2:00 PM=COUNTIF(A3:C3, “<“&TIME(12,0,0))1
47:00 AM2:00 PM3: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:

ABCDEF
1Start HourStart MinuteShift LengthEnd TimeFormulaResult
2908=IFERROR(TIME(A2+B2, C2, 0), “Error”)5:00 PM
3809=IFERROR(TIME(A3+B3, C3, 0), “Error”)5:00 PM
410010=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

1. 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.
2. 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.
3. 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.
4. Formatting time: You can format cells to display time in various