» Creating a World Time Converter

CATEGORY - Excel Date & Time Formulas VERSION - All Microsoft Excel Versions

Problem:

Finding the equivalent time for the city in cell C5 (Jerusalem) as that shown in cell D2 for the city in cell C2 (New York).

Solution:

1. Create a list of cities (column A) and their matching hourly difference (column B) from GMT (Greenwich Mean Time).
2. Use the VLOOKUP function as shown in the following formula in cell D5:
=(D2*24-VLOOKUP(C2,A2:B8,2,FALSE)+VLOOKUP(C5,A2:B8,2,FALSE))/24