The Excel TIME function combines separate hour, minute and second values and converts them into a time serial number. If the cell format was General before the formula is entered, the result is formatted as a date.
Syntax: =TIME(hour, minute, second)
|hour:||A number from 0 (zero) to 32767 representing the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value. For example, TIME(27,0,0) = TIME(3,0,0) = .125 or 3:00 AM.
|minute:||A number from 0 to 32767 representing the minute. Any value greater than 59 will be converted to hours and minutes. For example, TIME(0,750,0) = TIME(12,30,0) = .520833 or 12:30 PM.
|second:||A number from 0 to 32767 representing the second. Any value greater than 59 will be converted to hours, minutes, and seconds. For example, TIME(0,0,2000) = TIME(0,33,22) = .023148 or 12:33:20 AM.
Notice the last result shows the #NUM! error. This is because it results in a negative time and Excel can't calculate that. More on how to solve this later.
Note: If we add time that takes us past 24 hours it will add the excess time to 0 time, effectively starting the clock again. See examples below.
Excel Time Function Conundrum
Let's say and your local time in sunny Queensland, Australia right now is 4:00 AM (the sun is just coming up), and you want to find the time in New York.
You know your local time is GMT+10, and you know that the time in New York is GMT-5.
This is starting to sound like one of those maths questions you used to get in school. You know the ones that go something like:
“Michael is two years older than three times Jennifer's age. If Jennifer is ‘x’ years old, how would you calculate Michael's age?”
Don’t worry I wouldn’t do that to you. My question is much simpler…well maybe:
What formula would you use in Excel to calculate the local time in New York if it’s 4:00 AM in Queensland, Australia?
Here, I’ll help you out:
The calculation you want to do is:
=4:00AM – (10hrs + 5hrs)
And the answer is 1:00 PM.
While this example is easy in your head it’s not so straight forward in Excel.
Problems Calculating Time in Excel
- Excel can’t handle negative times. So taking the example above your formula would result in a negative time and all you’d get is ######.
- When you enter time in Excel you need to enter it in h:mm:ss format. Simply entering ‘10’ for 10 hours will confuse Excel.
Here’s my solution:
There are a few ways to tackle this, and the image below illustrates 2 options:
Rows 4 and 7 show the components of my calculation, and rows 5 and 8 show the behind the scenes view (from the formula bar) of what I actually typed in the cells above.
- Cell B4 – You can help Excel handle a negative time by including the date component.
- Cells C4 and D4 – I’ve used the TIME function to enter the hours I want to adjust by.
Tip: Notice in my example the TIME formula only has hours specified. I’ve left the minutes and seconds blank.
- Cell E4 – this contains my formula =B4-(C4+D4)
- Rows 7 and 8 show an alternate calculation that doesn't require the date to also be entered with the time. It uses the MOD function in cell E7 to avoid a negative time result.
Ok, you might be thinking you could have done all that in your head. But trust me. When you have family in England, Ireland and Indonesia and clients all over the world it can get pretty tiring (read ‘confusing’) constantly converting time.
For Extra Credit
I’ve actually taken it one step further (as you may expect) and incorporated a VLOOKUP table that lists all of the Time Zones.
And I’ve inserted drop down lists to select the time zone from my table with a named range of time_zone.
And then I used a VLOOKUP formula in column E like this one in cell E13:
Download the file here and use it yourself.
Of course if you only have one time zone that you’re constantly converting to then you can add a time zone to your Outlook calendar and see your local time and other time zone side by side.
Free eBook - Working with Date & Time in Excel
Everything you need to know about Date and Time in Excel - Download the free eBook and Excel file with detailed instructions.
Enter your email address below to download the comprehensive Excel workbook and PDF.