Excel Time Functions

Last Updated: April 20, 2023
puneet-gogia-excel-champs

- Written by Puneet

sample files

1. HOUR Function

HOUR function returns the hour value from a valid time. Time value is made up of, hours, minutes, and seconds and the HOUR function takes hour value and ignores the rest of all.

Syntax

HOUR(serial_number)

Arguments

  • serial_number: A valid time from which you want to get the hour value.

Example

1. Use Cell Reference

In the below example, we have referred to a cell in which I have a valid time value. The function has only extracted hour in 24-Hour format from that time value. You can use this method where you have time data and you want to extract hour from those values.

excel-hour-function-example-1

2. Use Date as a Reference

In the below example, we have used a date that has a time value in it and HOUR has extracted the hour as an integer from it.

excel-hour-function-example-2

And if you convert that date into value by changing its format, you’ll get to know that it has time value as decimals.

excel-hour-function-example-3

2. MINUTE Function

MINUTE function returns the minute value from a valid time. Time value is made up of, hours, minutes, and seconds and MINUTE function takes the minute value and ignores the rest of all.

Syntax

MINUTE(serial_number)

Arguments

  • serial_number: A valid time from which you want to get the minute value.

Example

In the below example, we have used the minute function to extract minute value from different time values.

excel-minute-function-example-1

3. NOW Function

NOW function returns the current date and time as per the system’s date and time. The date and time returned by the NOW function update continuously whenever you update anything in the worksheet.

Syntax

NOW()

Arguments

  • In NOW, there is no argument to specify, but you need to take care that this function is a volatile function which changes its value every time you update your worksheet’s calculation.

Example

Below we have used NOW in two different ways:

excel-now-function-example-1
  • In the first example, we have used TEXT Function along with now to extract current time from it.
  • In the second example, we have changed the format of the cell to “General”. The number before the decimal point is the current date & number after the decimal point is the time.

4. SECOND Function

SECOND function returns the seconds value from a valid time. Time value is made up of, hours, minutes, and seconds and SECOND function takes seconds value and ignores the rest of all.

Syntax

SECOND(serial_number)

Arguments

  • serial_number: A valid time from which you want to get the second value.

Example

In the below example, we have used different input value to get second. The input value should be a valid date and time as per Excel’s date and time system.

excel-second-function-example-1

An input value that has no value of seconds in it will return 0 as a result.

excel-second-function-example-2

5. TIME Function

TIME function returns a valid time based on the hours, minutes, and seconds you input. In simple words, you need to specify all the components of the time and it will create a time value with it and return in the result.

Syntax

TIME(hour,minute,second)

Arguments

  • hour: The number to use like an hour value.
  • minute: The number to use as a minute value.
  • second: The number to use as a second value.

Example

In the below example, we have used different arguments:

excel-time-function-example-1
  • Time returns by this function are in 12-hour format.
  • If you use zero for the hour value it will give you time from the starting hour of a day.
  • When you specify a negative number it will deduct that value from the previous hour, minute or second.
  • The time function returns a decimal number which represents a particular time in Excel.

6. TIMEVALUE Function

TIMEVALUE funtion returns time after converting a text which represents a time into actual time. In simple words, it converts time into an actual time which is formatted as text.

Syntax

TIMEVALUE(time_text)

Arguments

  • time_text: A text which is a time value but stored as a text.

Example

We need to try it out in an example, so make sure to check out the below one:

excel-timevalue-function-example-1

If you use a textual time value that has no am/pm in it, the function will return a serial number which represents time in 24-hour format. You can change the format of the serial number to get the time.

TIMEVALUE will convert any textual time value which is recognizable by excel in the decimal number. As 12:00 AM is the starting time of the day, the function has return 0 for it.

It represents the time from 12:00 am to 11:59 pm. It’s smart enough to get the time from a date if that date has time in it. While using TIMEVALUE all you have to take care of is a date that can recognizable by Excel.