How to Extract Time Values in Microsoft Excel

How to Extract Time Values in Microsoft Excel. In Microsoft Excel, many spreadsheets include date and time values, either separately or combined in the same cell. When working with these values, it is crucial to understand date arithmetic. In this tutorial, I will demonstrate how to enter time values in Excel and introduce three functions that will help you parse the hour, minute, and second components from a composite time value.

How to Extract Time Values in Microsoft Excel

Entering Time Values in Excel:

To enter Extract time values in Excel, you need to use a format that Excel recognizes. While you can enter the exact time as a decimal value, it is more common to enter time values in a format that is meaningful to you, such as “9:30 AM,” “12:30 PM,” or “8:01:01 AM.”

Although Excel stores time as a decimal value, you will enter time values as integers, separating the components with colons. If necessary, you can include a space and the AM/PM component. Military time does not require the AM/PM component.

For example, you can enter 7 PM as “7:00:00 PM.” If you want to enter 8 AM without additional components, you can enter “8:00:00 AM,” “8 AM,” or even “8 a.” If you find Excel’s display of Extract time values confusing, you can check the default format and consider changing it.

If you omit the AM/PM component, Excel assumes it is AM. For military time values, you do not need to enter the AM/PM component. However, if the format is not set to display military time, Excel will show the entry as an AM/PM time. The formula bar will display all three time components and AM/PM.

Parsing Hours in Excel:

To parse the hour component from a time value in Excel, you can use the HOUR() function. This function returns the hour as an integer from 0 to 23, where 0 represents 12 AM and 23 represents 11 PM.

The syntax for the HOUR() function is simple: HOUR(serial_value). The serial_value can be a time value entered directly, a reference to a time value, a text value in quotes (e.g., TIMEVALUE(“6:45 PM”)), a decimal value, a static time value, or the result of another function or expression.

Parsing Minutes in Excel:

To extract the minutes component from a time value, you can utilize the MINUTE() function in Excel. Similar to the HOUR() function, MINUTE() returns the minutes as an integer relative to the current hour.

The syntax for the MINUTE() function is the same: MINUTE(serial_value). You can use the MINUTE() function to retrieve the minutes component in the same ways as the HOUR() function.

Parsing Seconds in Excel:

To obtain the seconds component from a time value, you can employ the SECOND() function in Excel. The SECOND() function returns the seconds as an integer.

The syntax for the SECOND() function is: SECOND(serial_value). It follows the same usage patterns as the HOUR() and MINUTE() functions.

Converting Time Values to Decimal Values in Excel:

Internally, Excel stores time values as decimal values. To view the underlying decimal value, you can change the cell format to General or apply a custom format. This will reveal the decimal representation of the time value. Keep in mind that TODAY() only returns an integer value because it does not include a time component.

In future articles, I will demonstrate how to reference the formatted Extract time values in expressions without changing the format. This way, you can work with the Extract time values directly without altering their display.

If you’re interested in further exploring this topic, I recommend reading the article “How to Extract the Date and Time from a Serial Date in Excel.” It includes a section that explains date arithmetic, which can be helpful if you’re not familiar with it yet.

Stay tuned for upcoming articles where I will delve deeper into Excel’s time functions and provide more insights on working with Extract time values.