Time formula is a unique excel worksheet function which is used to calculate time with separate arguments provided for hour, minute and seconds respectively, the output generated by this function is time, this function can also be used as a nested function to calculate time for given arguments, the method to use this function is =time(Hour, Minute, Second).
Time Formula in Excel (Table of Contents)
Excel TIME Formula
In excel we have a built-in function called TIME to enter accurate dates. The Formula of the TIME excel function as follows.
The syntax is a common lie of how time consists of hour, minute, and second. So same thing included here as well.
- Hour: This is nothing but our hour part. You can enter an hour from 0 to 23. The moment it crosses 23 it will again start from 1. For example, if you enter 24 as the hour part, it will be divided by 24 and the remainder is 0, so TIME will be TIME (0,0,0). If you enter 25 as the time argument it will be divided by 24 and the remainder is 1, so time will be TIME (1,0,0)
- Minute: This is a minute part of the time. Every 60 second consists of 1 minute. The moment it crosses 60 minutes it will add to the hour part. For example TIME (0,60,0) is nothing but TIME (1,0,0). TIME (0,150,0) is equal to the TIME (2,30,0).
- Second: This is the second part of the time. Every 60 second is equal to 1 minute, so the moment it crosses 59 seconds it will add to the minute part. For example TIME (0,0,60) is equal to TIME (0,1,0) and TIME (0,0,150) is equal to the TIME (0,2,30).
What is TIME?
If you are following our posts regularly you must have come across different articles on excel DATE & TIME formats. Just to remind you “excel stores date & time as serial numbers”.
The date will be stored from number 1 and time will be stored in decimal points. In excel date & time are combined together. For an example look at the below image.
In cell A1 we have 0.56 as the value. For the same cell apply TIME format and see what happens.
So 0.56 is equal to 13:26:24 in time format. Similarly, now I have entered 0.99999 in the A2 cell.
Now I will apply a time format to it and see what happens.
It has given the time as 11:59:59, so this is the end of time in 12 hours a day. Using formatting techniques in excel, we can play with time format in excel. So in excel time numbers will be stored from 0 to 0.99999.
How to Use TIME Formula in Excel? (with Examples)
Following are the examples of TIME Formula in Excel.
For better understanding start the TIME format to enter the time of 12:50:30.
Open TIME function anywhere in the worksheet.
Enter hour as 12, minute as 50, and second as 30.
Now hit the enter key and see what happens.
It showing only the hour and minute part but it is not showing the second part. So press Ctrl + 1 and change the format to hh:mm:ss.
Now see the result.
Like this using formatting section, we can adjust the time. Below are some of the important formatting codes for time.
Assume you want to get today’s date automatically, what will you do. Yes, we have a built-in function called TODAY excel formula to do the job for us.
Enter TODAY function in any of the cell to get the TODAY’s date.
Now in order to get the current date and time we have one more built-in function called NOW in excel, which will return the current date and time together.
These two are volatile functions and keeps changing whenever the date or time changes in the system. NOW function will keep changing every second.
Example #3 – Date & Time are Combined in Excel
As I have stated earlier in the post, date & time work together in excel. Since Excel stores date in whole numbers and time infractions.
For example, 43523 is equal to the date 27-Feb-2019.
Now I will change the number from 43523 to 43523.085 and see what happens. I will change the format to dd-mmm-yyyy hh:mm:ss AM/PM.
So here 43523.085, 43523 is date portion and 0.085 is the time portion.
Example #4 – Difference between Two Times
In order to perform the difference between two times are just like our subtraction method. For example, look at the below two times.
Apply the Formula to see the Differences.
So the difference between these two times is 54 minute 35 seconds.
Now take an example of time with different dates. Consider below two dates for example.
Now I will apply the above technique to get the difference.
The actual difference between these two dates is 28 hours, but result showing as 4 hours. The reason behind this is the moment difference crosses 24 hours we need to change the format of the cell from hh:mm:ss to [hh]:mm:ss. We need to enclose the hour part with brackets.
Now see the result.
Things to Remember About TIME Formula in Excel
- When you are finding the difference between two times and if the difference crosses 24 hours we need to change the format from hh:mm:ss to [hh]:mm:ss.
- Date & Time are waked together in excel.
- The date represents the whole number and time represents the decimal part.
TIME Formula in Excel Video
This has been a guide to TIME Formula in Excel. Here we discuss how to use TIME formula in Excel with some examples and downloadable excel template. You may learn more about excel from the following articles –