Many Microsoft Excel spreadsheets store date and time values. Sometimes the two values appear in the same cell, sometimes not. If you work with dates and/or times, you need a good understanding of date arithmetic.
In short: an integer represents the date and a decimal represents the time. Regardless of where the values come from, you may need to analyze 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 will show you how to enter time values in Excel. Then we’ll look at three Microsoft Excel functions that you can use to 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 will not want to enter time values this way. Instead, we enter them in a way that is meaningful to us – 9:30 AM, 12:30 PM, 8:01:01 AM, and so on.
Although Excel stores time as a decimal, enter time values as integers and separate the components with colons. Where appropriate, follow the integers with a space and the AM/PM component. Military time does not require a final component.
You can enter a time for all components. For example, enter 7:00 PM as 7:00:00 PM. If you want to enter 8:00 AM without any other components, enter 8:00:00 AM, 8:00 AM, or even 8:00 AM. If you’re confused about how 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, do not enter the AM/PM component. If the format is not set to display military time, Excel displays the entry as AM/PM time. Excel’s formula bar shows all three time components and AM/PM.
The example data includes time values entered in various ways and the results of TODAY() and NOW(). The last two are volatile functions. Volatile functions are updated each time the sheet is computed. Consequently, the figures do not show the same times from figure to figure. You will see this behavior in your own sheet if you follow along.
Let’s now analyze time values.
How to analyze hours in Excel
If you don’t have a sheet of time values, you can use the downloadable demonstration file or use the instructions above to enter some time values similar to those shown in Figure A. Specifically, let’s use Excel’s HOUR() function to parse the hour from each value.
Excel’s HOUR() function returns the hour component of a time value as an integer ranging from 0 to 23, where 0 is 12:00 AM and 23 is 11:00 PM.
The syntax of the HOUR() function 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, e.g. B. 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 type the date. The date is an integer and the time is a decimal number that is part of the integer of the date. For example, 0.50 means half of the day or 12 noon.
Figure A shows the results of the HOUR() function. Column A identifies special values in column B that you can’t see because of the formatting.
As you can see, this function returns an integer representing 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 0.063 when 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 only returns the integer of the day. Excel’s NOW() function returns both the day and time, but a format only displays the time value in B11.
Now let’s analyze the minutes.
How to analyze logs in Excel
To analyze minutes, use Excel’s MINUTE() function, which, as expected, returns the minutes relative to the current hour of a time value. This function uses the same syntax as HOUR():
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 equals 37 minutes in the hour. TODAY() continues to return 0 and NOW() is updated with each calculation so it changes. in the Figure Bthe time is 40 minutes to the hour.
Our final time component to analyze is seconds.
How to analyze seconds in Excel
If you suspected Excel had a SECOND() function, you’re right. Excel’s SECOND() function returns the seconds of a time value and uses the following syntax:
Figure C shows the results of the SECOND() function in column E. Everything you’ve learned so far also applies to the SECOND() function. The time value 5:11:30 PM is 30 seconds to the minute. The decimal value 0.693 is 55 seconds per minute.
How to convert time values to decimal in Excel
Internally, Excel stores the time as a decimal value. The quickest way to display this 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() only returns an integer since it doesn’t return a time value. Notice that the formula bar now shows the time value as a decimal number.
Instead of changing the format to show the underlying decimal values, you may want to use an expression that references the formatted time values. It’s possible, and I’ll show you how in a future article.
If you want to learn more about this topic, read How to extract the date and time from a serial date in Excel. There is a short section to help you understand date arithmetic if you are new to it.