Excel 2007 : Creating world time converter

Hi,

I am attempting to create a world time converter for a meeting planner I have made. I am following the instructions, from another thread on your webiste as below, and have data in colums A1:D5 as below (far bottom)
http://www.excelforum.com/tips-and-t...converter.html
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

Data I have added for calculations:
AEST 10
BST 8 0:07:00
NZ 12
US CST -6
US PST -8 #N/A


The formulae is as in the instructions and I have been careful to sort the table also, the formulae keeps coming up as #N/A whatever I try...can anyone see what I'm doing wrong?
Is there a problem with the way I'm doing the V-lookup?
The way the instructions for the problem section aare written is a little confusing- maybe I have misinterpreted it?

Thanks,
Annie

Hi Annie

The instructions are correct although perhaps a little confusing. However the formula given will produce an error if the time returned is negative ie the previous day. For example when converting NZ to US PCT any time before 6pm in NZ will give an error. I have adjusted the formula to return the time plus an asterisk if it's the previous day.

=IF((D2*24-VLOOKUP(C2,A2:B8,2,FALSE)+VLOOKUP(C5,A2:B8,2,FALSE))/24<0,TEXT(24+(D2*24-VLOOKUP(C2,A2:B8,2,FALSE)+VLOOKUP(C5,A2:B8,2,FALSE))/24,"hh:mm")&"*",(D2*24-VLOOKUP(C2,A2:B8,2,FALSE)+VLOOKUP(C5,A2:B8,2,FALSE))/24)

See the attached file