How to parse time values in Microsoft Excel

Many Microsoft Excel sheets store date and time values. Sometimes the two values appear in the same cell, and sometimes they do not. If you work with dates and/or times, you need a good understanding of date arithmetic.

Briefly: An integer represents the date, and a decimal value represents the time. Regardless of where the values come from, you might need to parse the individual time components, hours, minutes and seconds.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

In this tutorial, I’ll show you how to enter time values in Excel. Then, we’ll review three Microsoft Excel functions that will help you parse the hour, minute and second values from a composite time value.

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions of Excel. Excel for the web supports time functions.

How to enter time values in Excel

When entering time values, you must enter them in a format that Excel recognizes. If you know the exact time, you can enter it as a decimal value. Most of us won’t want to enter time values that way. Instead, we’ll enter them in a way that has meaning to us — 9:30 AM, 12:30 PM, 8:01:01 AM and so on.

Even though Excel stores time as a decimal value, you’ll enter time values as integers, separating the components with colon characters. When appropriate, you’ll follow the integers with a space and the AM/PM component. Military time doesn’t require the final component.

You can enter a time with all components. For example, enter 7 PM as 7:00:00 PM. If you’d like to enter 8 AM without any other components, enter 8:00:00 AM, 8 AM or even 8 a. If you’re confused by the way Excel displays time values, check the default format. You might want to change it.

If you omit the AM/PM component, Excel assumes AM. When entering military time values, you won’t enter the AM/PM component. If the format isn’t set to display military time, Excel will display the entry as an AM/PM time. Excel’s formula bar will display all three time components and AM/PM.

The example data includes time values entered in several ways and the results of TODAY() and NOW(). The last two are volatile functions. Volatile functions update every time the sheet calculates. Consequently, figures will not show the same times from figure to figure. You’ll see this behavior happening in your own sheet if you’re following along.

Now, let’s move on to parsing time values.

How to parse hours in Excel

If you don’t have a sheet with time values, you can use the downloadable demonstration file or use the instructions above to enter a few time values similar to those shown in Figure A. Specifically, let’s use Excel’s HOUR() function to parse the hour from each value.

Figure A

Excel’s HOUR() function returns the hour component of a time value as an integer, ranging from 0 to 23 where 0 equals 12 AM and 23 equals 11 PM.

The HOUR() function’s syntax is simple — HOUR(serial_value) — where serial_value is a time value or a reference to a time value. You can enter the time value as text with quotes, such as TIMEVALUE(“6:45 PM”), a decimal value, a static time value, or even the result of another function or expression.

Time values are part of a date value, even if you don’t enter the date. The date is an integer, and the time is a decimal, which represents some part of the date integer. For instance, .50 represents halfway through the day, or 12 noon.

Figure A shows the results of the HOUR() function. Column A denotes special values in column B that you can’t discern because of formatting.

As you can see, this function returns an integer that represents the hour of the day represented by the time value in column B. The time value in B3, 5:11:30 PM, is 17 hours past midnight. The decimal value .063, if formatted would be approximately 4:30 PM, which is 16 hours past midnight. The TODAY() function, formatted as a time value is 0 because the TODAY() function returns only the day integer. Excel’s NOW() function returns both the day and time, but a format displays only the time value in B11.

Now let’s parse the minutes.

How to parse minutes in Excel

To parse minutes, use Excel’s MINUTE() function, which as you might expect, returns the minutes, relative to the current hour, of a time value. This function uses the same syntax as HOUR():

MINUTE(serial_value)

Figure B shows the results of the MINUTE() function in column D. The time value, 5:11:30 PM, is 11 minutes into the hour. The decimal value, 0.693 is 37 minutes into the hour. TODAY() continues to return 0 and NOW() updates with each calculation, so it will change. In Figure B, the time is 40 minutes into the hour.

Figure B

Our last time component to parse is seconds.

How to parse seconds in Excel

If you guessed that Excel has a SECOND() function, you’re right. Excel’s SECOND() function returns the seconds of a time value and uses the following syntax:

SECOND(serial_value)

Figure C shows the results of the SECOND() function in column E. Everything you’ve learned so far is also true of the SECOND() function. The time value 5:11:30 PM is 30 seconds into the minute. The decimal value .693 is 55 seconds into the minute.

Figure C

How to convert time values into decimal values in Excel

Internally, Excel stores the time as a decimal value. The quickest way to see that time value is to change the format. Figure D shows the result of changing the format for the time values in column B from Time to General. TODAY() returns only an integer because it doesn’t return a time value. Note that the formula bar now displays the time value as a decimal.

Figure D

Stay tuned

Instead of changing the format to see the underlying decimal values, you might want to use an expression that references the formatted time values. Doing so is possible, and I’ll show you how in a future article.

If you’d like to learn more on this subject, read How to extract the date and time from a serial date in Excel. There’s a short section that will help you understand date arithmetic if you’re not familiar with it yet.