Many Microsoft Excel sheets retailer date and time values. Typically the 2 values seem in the identical cell, and typically they don’t. If you happen to work with dates and/or instances, you want an excellent understanding of date arithmetic.
Briefly: An integer represents the date, and a decimal worth represents the time. No matter the place the values come from, you would possibly have to parse the person time parts, hours, minutes and seconds.
SEE: Home windows, Linux, and Mac instructions everybody must know (free PDF) (TechRepublic)
On this tutorial, I’ll present you how one can enter time values in Excel. Then, we’ll assessment three Microsoft Excel features that may enable you parse the hour, minute and second values from a composite time worth.
I’m utilizing Microsoft 365 on a Home windows 10 64-bit system, however you should use earlier variations of Excel. Excel for the net helps time features.
How one can enter time values in Excel
When coming into time values, it’s essential to enter them in a format that Excel acknowledges. If you recognize the precise time, you possibly can enter it as a decimal worth. Most of us gained’t wish to enter time values that manner. As a substitute, we’ll enter them in a manner that has which means to us — 9:30 AM, 12:30 PM, 8:01:01 AM and so forth.
Regardless that Excel shops time as a decimal worth, you’ll enter time values as integers, separating the parts with colon characters. When acceptable, you’ll comply with the integers with an area and the AM/PM element. Navy time doesn’t require the ultimate element.
You may enter a time with all parts. For instance, enter 7 PM as 7:00:00 PM. If you happen to’d wish to enter 8 AM with out another parts, enter 8:00:00 AM, 8 AM and even 8 a. If you happen to’re confused by the way in which Excel shows time values, test the default format. You would possibly wish to change it.
If you happen to omit the AM/PM element, Excel assumes AM. When coming into army time values, you gained’t enter the AM/PM element. If the format isn’t set to show army time, Excel will show the entry as an AM/PM time. Excel’s system bar will show all three time parts and AM/PM.
The instance knowledge consists of time values entered in a number of methods and the outcomes of TODAY() and NOW(). The final two are unstable features. Risky features replace each time the sheet calculates. Consequently, figures won’t present the identical instances from determine to determine. You’ll see this conduct taking place in your individual sheet should you’re following alongside.
Now, let’s transfer on to parsing time values.
How one can parse hours in Excel
If you happen to don’t have a sheet with time values, you should use the downloadable demonstration file or use the directions above to enter just a few time values just like these proven in Determine A. Particularly, let’s use Excel’s HOUR() perform to parse the hour from every worth.
Determine A
Excel’s HOUR() perform returns the hour element of a time worth as an integer, starting from 0 to 23 the place 0 equals 12 AM and 23 equals 11 PM.
The HOUR() perform’s syntax is easy — HOUR(serial_value) — the place serial_value is a time worth or a reference to a time worth. You may enter the time worth as textual content with quotes, reminiscent of TIMEVALUE(“6:45 PM”), a decimal worth, a static time worth, and even the results of one other perform or expression.
Time values are a part of a date worth, even should you don’t enter the date. The date is an integer, and the time is a decimal, which represents some a part of the date integer. For example, .50 represents midway by means of the day, or 12 midday.
Determine A reveals the outcomes of the HOUR() perform. Column A denotes particular values in column B that you could’t discern due to formatting.
As you possibly can see, this perform returns an integer that represents the hour of the day represented by the point worth in column B. The time worth in B3, 5:11:30 PM, is 17 hours previous midnight. The decimal worth .063, if formatted can be roughly 4:30 PM, which is 16 hours previous midnight. The TODAY() perform, formatted as a time worth is 0 as a result of the TODAY() perform returns solely the day integer. Excel’s NOW() perform returns each the day and time, however a format shows solely the time worth in B11.
Now let’s parse the minutes.
How one can parse minutes in Excel
To parse minutes, use Excel’s MINUTE() perform, which as you would possibly count on, returns the minutes, relative to the present hour, of a time worth. This perform makes use of the identical syntax as HOUR():
MINUTE(serial_value)
Determine B reveals the outcomes of the MINUTE() perform in column D. The time worth, 5:11:30 PM, is 11 minutes into the hour. The decimal worth, 0.693 is 37 minutes into the hour. TODAY() continues to return 0 and NOW() updates with every calculation, so it should change. In Determine B, the time is 40 minutes into the hour.
Determine B
Our final time element to parse is seconds.
How one can parse seconds in Excel
If you happen to guessed that Excel has a SECOND() perform, you’re proper. Excel’s SECOND() perform returns the seconds of a time worth and makes use of the next syntax:
SECOND(serial_value)
Determine C reveals the outcomes of the SECOND() perform in column E. All the things you’ve realized to this point can also be true of the SECOND() perform. The time worth 5:11:30 PM is 30 seconds into the minute. The decimal worth .693 is 55 seconds into the minute.
Determine C
How one can convert time values into decimal values in Excel
Internally, Excel shops the time as a decimal worth. The quickest method to see that point worth is to vary the format. Determine D reveals the results of altering the format for the time values in column B from Time to Common. TODAY() returns solely an integer as a result of it doesn’t return a time worth. Word that the system bar now shows the time worth as a decimal.
Determine D
Keep tuned
As a substitute of fixing the format to see the underlying decimal values, you would possibly wish to use an expression that references the formatted time values. Doing so is feasible, and I’ll present you the way in a future article.
If you happen to’d wish to study extra on this topic, learn How one can extract the date and time from a serial date in Excel. There’s a brief part that may enable you perceive date arithmetic should you’re not conversant in it but.
