offers Excel lesson videos made simple!

Sign up or log in to Magoosh Excel.

How to Use the Time Function in Excel

Microsoft Excel comes with various helpful Time function features, which, if known, can really save you a lot of time. Through this function, date and time values inserted in a cell gets converted to a specific decimal number. This decimal value can help you to get varied results because now you can perform numerous operations over this returned value.

A built-in function, categorized as a ‘Date/Time Function’, the Time function can be used as a worksheet function and can also be inserted as a part of a formula in a cell of a worksheet.

Description

Excel stores every dates as integers and time values as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system.

In Excel’s Time function system, the serial number 1 corresponds to 01/01/1900 12:00:00 AM. Time values are stored as decimal numbers between .0 and 0.99999, where .0 is 00:00:00 and 0.99999 is 23:59:59. The date value and time value can be combined to create numbers that have a decimal and an integer portion. For example, if a decimal number is 32331.06, it represents the date and time 07/07/1988 1:26:24 a.m.

To help one perform some of the complex date and time calculations, Excel includes a wide array of built-in date and time functions.

Utility

It enables one to convert text entries representing various values of times of day into time serial numbers, so that one can use them in different calculations.

The Time function also comes with different functions which can combine different parts of a time into a single serial time number, as well as certain others which help in extracting the hours, minutes, and seconds from a single time serial number.

Syntax

TIME(hour, minute, second)

The TIME function syntax has the following three arguments:

  1. Hour: Required. It can be any number in between 0 (zero) to 32767. If the value higher than 23, gets divided by 24 and only the remainder will be considered as an hour value. For example, TIME(30,0,0) = TIME(6,0,0) =0 .2500 or 6:00 AM.
  2. Minute: Required. Any number, between 0 (zero) to 32767 is acceptable. Although if the value higher than 59 is converted to corresponding hours and minutes. For example, TIME(0,1000,0) = TIME(16,40,0) = 0.69444 or 4:40 PM.
  3. Second: Required. Similar to above two, any number between 0 (zero) to 32767 is acceptable. If the value higher than 59 is converted to corresponding hours, minutes, and seconds. For example, TIME(0,0,3000) = 0.03472 or 12:50:00 AM.

Note

  • Time values are a sub-part of a date value and are represented by a decimal number (for instance, 12:00 P.M. is regarded as 0.5, as it is half of a day).
  • Like every Excel time value, the time rendered from the Excel Time function is stored within Excel, as a decimal (.). Therefore, if one wants the result of the function to be displayed as a time, one has to make sure that the cell containing the function is formatted with an Excel ‘Time’ format.
  • Time function works with most of the versions of Excel like Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010 etc.

Example

To see the things in action just copy the given example data (in the following table), and paste it into cell A1 of a new Excel worksheet. For formulas to show desired results, select them, press F2, and then press Enter. If you need to, you may increase or decrease the column widths to see all the data properly.

time function in excel -magoosh

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!