In this chapter, we will look at some of the commonly used time functions provided by Excel.
In the time functions in this chapter, the argument time can be
a literal date-time value,
an Excel function returning a date-time,
a cell reference where the cell contains a date-time, or
a named range containing a date-time value.
Let us begin exploring the most commonly used time functions.
NOW Function
The NOW function returns the system date and time at the point this function is executed.
Syntax
The NOW function does not take any argument.
Example
NOW function
In cell B2 we have used the function =NOW(). If you try out this function on your system, it will give a different output.
HOUR Function
The HOUR function returns the hour part from a date-time value. The value returned will be between 0 and 23. The hour is always returned in 24-hour format.
Syntax
A date-time value containing a time component
Example
HOUR function
Cell B2 contains the value 24-08-2020 16:43:15. In cell C3, we have used the function =HOUR(B2). This will return the value 16, the hour component.
MINUTE Function
The MINUTE function returns the minute part from a date-time value. The value returned will be between 0 and 59.
Syntax
A date-time value containing a time component
Example
MINUTE function
Cell B2 contains the value 24-08-2020 16:43:15. In cell C3, we have used the function =MINUTE(B2). This will return the value 43, the minute component.
SECOND Function
The SECOND function returns the second part from a date-time value. The value returned will be between 0 and 59.
Syntax
=SECOND(date-time)
A date-time value containing a time component
Example
SECOND function
Cell B2 contains the value 24-08-2020 16:43:15. In cell C3, we have used the function =SECOND(B2). This will return the value 15, the seconds component.
TIME Function
The TIME function is used to create a time using the hour, minute, and second specified by the user.
Syntax
The first argument is the hour part in numeric format.
The second argument is the minute part in numeric format.
The third argument is the seconds part in numeric format.
Example
TIME function
In cell B2, we have used the function =TIME(15,35,20). This gives the value 3:35 PM. Mind you, I have formatted cell B2 to make time appear in a human-understandable format. The format used for this example is “hh:mm AM/PM.”
TIMEVALUE Function
The TIMEVALUE function is used to create a time using a text string in the correct format.
Syntax
A text string in an acceptable format for converting to time format
Example
TIMEVALUE function
In cell B2, we have used the function =TIMEVALUE("15:45:30"). This will create the value as shown in cell B2 in Figure 4-6. In this example, I have formatted cell B2 to show the value appropriately. The format used in cell B2, for this example, is “dd:mm:ss AM/PM.”
Summary
To summarize, in this chapter we looked at some of the time functions provided by Excel. As always, I suggest you try out the examples from this chapter using your own data and also using the different options for the arguments. This will give you more clarity regarding how the functions actually work.
In the next chapter, we will look at Excel tables.