How to Use HOUR, MINUTE, SECOND, and TIME in Excel

Introduction

Your call log tracks every call down to the minute, but the staffing report just needs the hour. HOUR, MINUTE, and SECOND pull a timestamp apart, and TIME builds one from scratch.

The Dataset

Here's a daily call log for a small support team:

	A	B	C
3	Call	Start	Length (min)
4	501	8:15 AM	27
5	502	8:42 AM	12
6	503	10:30 AM	45
7	504	1:10 PM	35
8	505	4:05 PM	50
9	506	4:25 PM	18
10	507	4:48 PM	8

The team lead needs to know which hours are busiest so she can adjust the afternoon schedule.

HOUR

HOUR returns the hour from a time value as a number from 0 to 23. The syntax is:

=HOUR(serial_number)

serial_number: A cell containing a time or datetime.

=HOUR(B4)

The result is 8. The first call came in during the 8 AM hour.

Call	Start	Hour
501	8:15 AM	8
502	8:42 AM	8
503	10:30 AM	10
504	1:10 PM	13
505	4:05 PM	16
506	4:25 PM	16
507	4:48 PM	16

HOUR uses 24-hour time. 1:10 PM returns 13, not 1. That trips people up the first time, but it makes grouping clean because every hour gets a unique number.

Two calls landed in the 8 AM hour, one at 10 AM, one at 1 PM, and three in the 4 PM hour. If the team has one person on phones after 3, that 4 PM cluster is the problem.

HOUR returns a number, not a formatted time. If you want a label like "8 AM" for a chart, wrap it in TEXT:

=TEXT(B4, "h AM/PM")

This returns 8 AM. For 1:10 PM, it returns 1 PM.

Key takeaway: HOUR pulls the hour from any timestamp as a number from 0 to 23. Pair it with COUNTIFS to count activity by hour, or use it in an IF to flag calls outside business hours.

MINUTE

MINUTE returns the minute portion, from 0 to 59:

=MINUTE(serial_number)

=MINUTE(B4)

The result is 15. The first call started 15 minutes into the 8 AM hour. For call 505, MINUTE(B8) returns 5 (4:05 PM).

If the team's shifts change on the half hour, =MINUTE(B4) >= 30 tells you whether a call came in during the first or second half of the hour.

SECOND

SECOND returns the seconds portion, from 0 to 59:

=SECOND(serial_number)

Most business timestamps don't include seconds, so SECOND returns 0 for every call in this log. It shows up more in server logs and transaction records where seconds actually matter.

TIME

The other three functions take a timestamp apart. TIME does the reverse, building a time value from separate numbers:

=TIME(hour, minute, second)

hour: 0 to 23.
minute: 0 to 59.
second: 0 to 59.

The call lengths in column C are plain numbers, not time values. To convert 27 minutes into something you can add to a start time:

=TIME(0, C4, 0)

This converts 27 minutes into a time value. Add it to the start time to get when each call ended:

=B4 + TIME(0, C4, 0)

For call 501, that's 8:15 AM + 27 minutes = 8:42 AM.

Call	Start	Length (min)	End
501	8:15 AM	27	8:42 AM
502	8:42 AM	12	8:54 AM
503	10:30 AM	45	11:15 AM
504	1:10 PM	35	1:45 PM
505	4:05 PM	50	4:55 PM
506	4:25 PM	18	4:43 PM
507	4:48 PM	8	4:56 PM

TIME also handles the reverse situation, where hours and minutes are already in separate columns. If a shift schedule has 14 in one column and 30 in another, =TIME(14, 30, 0) returns 2:30 PM.

Key takeaway: TIME builds a time value from separate numbers. Use it to convert plain minutes into a format Excel can add and subtract, or to combine hour and minute columns into a single timestamp.

Conclusion

HOUR, MINUTE, and SECOND extract the pieces of a timestamp. TIME puts them together. Once the pieces are in their own column, COUNTIFS and SUMIFS handle the grouping. For extracting the year, month, or day of the week from dates instead of times, see our guide on YEAR, MONTH, DAY, and WEEKDAY. For calculating differences between dates or finding business days, check out our guide on DATE, DATEDIF, EDATE, and NETWORKDAYS.