Excel TIME Function Explained

Here’s a problem for you. You want to find out the time in another time zone.  Let’s say New York. Your local time in sunny London, UK right now is 4:00 AM (the sun is just coming up). You know your local time is GMT+ 0, 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 Deborah's age. If Deborah 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 London, UK? Lets show you how to do it: The calculation you want to do is: =4:00AM  –  (0hrs + 5hrs) And the answer is 1:00 PM. So easy to work out on paper but not that straight forward when using excel. Calculating Time in Excel Known Problems 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. My resoloution: Row 4 shows components of my calculation, and row 5 shows the behind the scenes view (from the formula bar) of what I actually typed in the cells above. Remember British summer time not allowing for that will give you the wrong result. Cell B4 – For Excel to calculate a negative time you actually need to also give that time a date. It can be any date because you’ll format the cell to h:mm AM/PM and the date won’t be visible anyway. To be exact type =now() Cells C4 and D4 – I’ve used the TIME function here. The syntax for the TIME function is: =TIME(hour,minute,second)  Notice in my example I only have hours and so I’ve left the minutes and seconds blank. Cell E4 – this contains my formula =B4-(C4+D4) The example above only works with positive numbers the example below handels both positive and negative time zones. Alternative Method incorporate a VLOOKUP table that lists all of the Time Zones. insert drop down lists to select the time zone from my table with a named range of time_zone. (see data validation) And then I used a VLOOKUP formula in column E like this one in cell E13: =TIME((HOUR(B13))+VLOOKUP(D13,zone,2,FALSE),MINUTE(B13),) Of remember 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.

Excel can be fun try out some of its features Detailed on this Site

Mike Barrett (MCT) Senior Training Advisor