TIME in Excel (Table of Contents)
TIME in Excel
Time function in excel is just another time function that returns HHMM AM/PM Format time. We just need to feed Hour, Minute and Second as per our requirement, and the time function will exactly return the same value in AM and PM details. One thing we need to note, that if the Hours’ range is less than and equal to 12, then the time which we get would be in AM and post 12; the time would be in PM till 11:59 in the clock.
TIME Formula in Excel:
The Formula for the TIME Function in Excel is given below.
Arguments:
- Hour: If the hour value is greater than 23, it will be divided by 24, and the remainder will be used as the hour value. This means that TIME(24,0,0) is equal to TIME(0,0,0) and TIME(25,0,0) is equal to TIME(1,0,0).
- Minute: If the minute value is greater than 59, then every 60 minutes will add 1 hour to the hour value. This means that TIME(0,60,0) is equal to TIME(1,0,0) and TIME(0,120,0) is equal to TIME(2,0,0).
- Second: If the second value is greater than 59, then every 60 seconds will add 1 minute to the minute value. This means that TIME(0,0,60) is equal to TIME(0,1,0) and TIME(0,0,120) is equal to TIME(0,2,0)
Steps to Open TIME Function in Excel
We can enter the time in a normal time format so that Excel will take the default one by default. Please follow the below step by step procedure. Choose time format in the Format Cells dialog; you may have noticed that one of the formats begins with an asterisk (*). This is the default time format in your Excel.
To quickly apply the default Excel time format to the selected cell or a range of cells, click the drop-down arrow in the Number group, on the Home tab, and select Time.
To change the default time format, go to the Control Panel and click Region and Language. If in your Control panel opens in Category view, click Clock, Language, and Region > Region and Language > Change the date, time, or number format.
How to Use TIME Function in Excel?
This TIME Function in Excel is very simple easy to use. Let us now see how to use the TIME Function in Excel with the help of some examples.
In the above example, we have three columns Decimal, Hours: Minutes, Hours: Minutes: Seconds with three different format which show the TIME function result.
Decimal
We have used the same TIME Function in this format and displayed used General (Decimal) format. In order to convert into a decimal format, right-click and choose format cells and then choose general.
Hours: Minutes
The second format we have used is Hours & Minutes; in order to display in the above format, right-click and choose format cell there, we will find the custom option where we will get a variety of hours formats to choose the appropriate format for H:MM AM/PM.
Hours: Minutes: Seconds:
The third format is Hours: Minutes: Seconds, where we have used the same TIME function to display it. In order to create a right-click and choose format cell there, we will find the custom option where we will get a variety of hours formats to choose the appropriate format for H:M:SS AM/PM.
This time function can be useful in all productivity scenarios. Remember that the TIME function will “roll over” back to zero when values exceed 24 hours, wherein the advanced MOD function can be exactly used to differentiate AM/PM, and this MOD function will take care of the negative numbers problem. MOD will “flip” negative values to the required positive values.
For example, if the employee works for 9 hours and he extended his Overtime for another 5 hours. In this scenario, if we want to find the start time and end time, we can use the MOD function to calculate how many hours an employee has been worked.
What is MOD Function?
The MOD function is defined as “Returns the remainder after a number is divided by a divisor.”
The Formula for MOD Function:
=MOD(number, divisor)
To calculate the date and time difference in Excel is a common task. Unfortunately, depending on requirements, it is also not always a simple one.
In the below example where column A contains a start date and time, and column B an end date and time. We wish to calculate the elapsed time in days, hours and minutes, e.g. 11 days 4 hours 9 minutes.
There are multiple ways of calculating the date and time difference in Excel. In this scenario, we will need to get a little clever. As you may well know, date and time values are stored as numbers in Excel. For example, the 05/01/2018 10:10 is stored as 2.993055556.
Therefore, if I write the formula as =B2-A2.
Then the result is returned as 2.993056.
To return a result that makes sense to us, we will tackle the date and time parts of the cell separately.
Calculating the Number of Days Difference
To work with just the date part of the cell, we will use the INT function. This function rounds a value down to the nearest integer. So if we write the function below, this will return only the integer part of the date difference, which is the number of days.
=INT (B2-A2)
Then the result is returned as 2.
Calculating the Elapsed Hours and Minutes
We now need to work on the number of hours and minutes, which is the decimal part. To return only the decimal part of the B9-A9 formula, we will use the MOD function. This function returns the remainder after a number is divided by a divisor.
We will use it to divide the B9-A9 formula by 1 so that it returns to the remainder as the decimal part. We will then use the HOUR and MINUTE functions to return the hours and minutes from this decimal value.
So the formula below returns the number of hours elapsed.
=HOUR(MOD(B9-A9,1))
Then the result is returned as 16.
And this returns the number of minutes elapsed.
=MINUTE(MOD(B14-A14,1))
Then the result is returned as 50.
A formula for Elapsed Time in Days, Hours and Minutes
Finally, we need to put this all together as one Excel formula. We can use the ampersand (&) to concatenate the different parts of the formula. You can construct the result to look however you want. For example, the formula below would return the result as 2 days, 23 hours and 50 minutes.
=INT(B2-A2)&” Days “&HOUR(MOD(B9-A9,1))&” Hours “&MINUTE(MOD(B14-A14,1))& ” Minutes ”
It will return the result as 2 days, 16 hours and 50 minutes.
Recommended Articles
This has been a guide to TIME in Excel. Here we discuss the TIME Formula in Excel and how to use TIME Function in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
120+ Online Courses | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9
View Course
Related Courses
