Excel 2013 & 2016: Time of Day Functions

Updated 2013.08.07

Objectives

        Understand how times are represented internally in Excel.

        Learn how to format times and dates.

        Use the NOW() function to get the current date and time.

        Use the HOUR(), MINUTE(), and SECOND() functions to extract individual fields from an Excel time.

        Use the TIME() function to create a time from an hour, minute, and second value.

How Excel Stores Dates And Times

Excel stores dates using a serial number.

The serial number 1 is January 1, 1900, serial number 2 is January 2, 1900, etc., like this:

1

1/1/1900

2

1/2/1900

3

1/3/1900

4

1/4/1900

5

1/5/1900

Etc.

 

January 1, 2000, is 36,526 (note that this is 365 days * 100 years + 25 leap year days + 1).

Times are stored as a fraction of a day. For example, .5 is half a day, or 12:00 noon. .75 is three-fourths of a day, or 6:00 p.m., etc.

Dates and times are combined the integer part of a serial number represents the date, and the fractional part of a serial number represents the time of day. So 12 noon on January 1, 2000 is represented as 36,526.50.

The HOUR(SerialNumber) function

The HOUR() function requires a single argument that must be a date/time serial number. It will return the number of hours represented by the time (a number between 0 [12 AM] and 23 [11 PM]).

Example

The time 12:15 PM has been entered in B2 below. The hour part of this number is 12. The minute part of this number is 15. If we put the formula =HOUR(B2) in C2:

image001

It will return the hour value of the time in B2, which is 12.

The MINUTE(SerialNumber) function

The MINUTE() function requires a single argument that must be a date/time serial number. It will return the number of minutes represented by the time (a number between 0 and 59).

Example

The time 12:15 PM has been entered in B2 below. The hour part of this number is 12. The minute part of this number is 15. If we put the formula =MINUTE(B2) in D2:

image003

It will return the minute value of the time in B2, which is 15.

The SECOND(SerialNumber) function

There is also a SECOND() function that will return the seconds portion of a time. It works the same way as the HOUR() and MINUTE() functions do, but is probably less useful.

Computing elapsed hours

You can use Excel to compute elapsed hours (e.g. to compute number of hours worked when given the time a person clocked in and clocked out). To do so, enter the start time and end time in separate cells. Subtract the start time from the end time. This will give you the fraction of a day that has elapsed. Multiply this by 24 to get the number of hours.

Example

A person clocks in at 6:00 am, and clocks out at 12:00 pm (noon). 6:00 am is represented as .25 (a fourth of a day has elapsed by 6:00 am), and 12:00 pm is represented as .5 (half a day has elapsed by 12:00 pm). The amount of time between 6:00 am and 12:00 pm is .5 - .25 = .25, which is a fourth of a day. Multiply .25 times 24 hours in a day, and you get 6 hours. In the example below, we are subtracting the two times, but Excel is trying to be smart and guess the format. Because B1 and B2 are formatted as times, it also formats B3 as a time. However, we want the number of hours, not the time.

If we change the format of B3 to General, we get this:

From 6:00 a.m. to 12:00 p.m. is one fourth of a day (0.25). But in this case, we probably want our answer to be in hours, so we need to multiply the fraction of a day by the number of hours in a day:

The TIME(Hour, Minute, Second) function

There is also a TIME() function that will return the serial number represented by the given hour, minutes, and seconds value.

Computing elapsed hours

If you already have values for the hour and minute for a given time, you can convert those numbers into a Date/Time serial number.

Example

You already have the following values in a spreadsheet representing the components of a time (cells B1 and B2). The formula in B4 will produce the time represented by 15 hours and 30 minutes (and 0 seconds). Even though the seconds value may not be important, it is required. If you do not have access to the seconds value, just use a 0 for the third argument to the TIME() function.

The above formula in B4 will produce the following result: