How to Use All Different Time Formulas in Google Sheets

0
276
Time Formulas in Google Sheets

Understanding time functions is very important when you have to handle salary, payroll, etc. Similar to Date Functions, time is also an important factor in such calculations. So let us learn all different time formulas in Google Sheets.

If you are not yet familiar with date functions in Google Sheets, switch to our date formulas guide.

Tutorial: Learn All Google Sheets Date Formulas

Master the below all different time formulas in Google Sheets so that you can face any time related calculations with ease.

When we talk about all different time formulas, there are seven functions in Google Sheets related to time. All are easy to use and understand.

Time Formulas in Google Sheets

1. TIME – Use TIME function to return provided hour, minute and second in to time.

Syntax: TIME(hour, minute, second)

Example to TIME function.

You can enter hours, minute and second components in different cells and combine them as a date using the above function.

Google Sheets time function

=TIME(A2,B2,C2)

Another example to the above Time formula is;

=TIME(10,35,59)

2. Google Sheets NOW Function – This function returns current date and time.

Here you may normally raise the following questions.

  • How to only get current time in Google Sheets?
  • How to remove date from the time or Now function?

I will answer to the above too here.

Syntax: NOW()

The above Time function will return the current time and date in Google Sheets. It’s a volatile functions.

See the result of the above function. It will be as follows as per the current time.

03/10/2017 10:18:49

Then what is a volatile function in Google Sheets?

A volatile function updates automatically in the cell where it resides. It updates every time you edit the spreadsheet.

Now I will tell you answer to your above questions that how to only show current auto updating time in Google Sheets.

There are two options to extract current time from the time and date format.

1. First by using Time formula and the format option combined.

First apply the below formula to get the time value.

=NOW()-TODAY()

Then Go to Google Sheets Formats menu Number and select the time format.

2. Using formula

=TEXT(NOW(),”hh:mm:ss”)

3. HOUR – Use this Google Sheets Time formula to return hour component from given time.

Syntax: HOUR(time)

See the below two example formulas.

=HOUR(“11:40:59”)

=HOUR(TIME(A2,B2,C2)) – here please refer TIME function example above

4. MINUTE – This function is similar to HOUR function. It returns the minute component of a given time. So I don’t want to repeat it here. Just the syntax.

Syntax: MINUTE(time)

5. SECOND – This function is also similar to HOUR and MINUTE function. It returns the seconds component of a given time. Just see the syntax.

Syntax: SECOND(time)

6. TIMEVALUE – This formula is to return the time value, i.e. fraction of a 24-hour day the time represents.

Syntax: TIMEVALUE(time_string) 

You can use this formula in either 24 hr format or 12 hr format.

=TIMEVALUE(“11:10:15”)

=TIMEVALUE(“11:10:15 AM”)

The above formula result will be the value “0.4654513889”.

This value you can use for your different time related operations. When you want to convert this numeric value back to time use the text formula as;

=text(E24,”hh:mm:ss”)

7. TO_PURE_NUMBER – You may be new to this function. Learn how to use TO_PURE_NUMBER function in Google Sheets below.

Syntax: TO_PURE_NUMBER(value)

This function convert and returns a pure number without formatting from a provided date/time, percentage, currency or other formatted numeric value. Didn’t get? See the below example.

Suppose in cell D2 there is time entered as 11:10:15. When you use the below formula you will get the time value of that time.

=TO_PURE_NUMBER(D2)

The result will be “0.4654513889”. This is similar to the TIMEVALUE function above. The difference is above we use string element in the formula, here time reference.

Also TO_PURE_NUMBER formula is not limited to time. This is similar to applying Format > Number > Normal formatting option.

LEAVE A REPLY

Please enter your comment!
Please enter your name here