Calculating length of flight times

[jstarr]

I'm working on putting together a spreadsheet to compare some flights.

The Columns will be:

A- Start Date
B- Start Time (formatted in AM/PM)
C- End Date
D- End Time
E- Length of Flight

I want column E to show Hours/Minutes.

I've had no luck getting the formula to work. I've tried doing (C2+D2)-(A2+B2) but it doesn't work for some reason.

 

[Andrew Poulsom]

Did you format the cell as [h]:mm to show hours greater than 24?

 

[jstarr]

Bingo!

I didn't even think of this other item. The flights would be across time zones.

For Example, NYC to Chicago, Chicago to Hong Kong. Is there a way to use VLookups to adjust the times to a standard time zone for lookup purposes to then calculate the length of hours?

 

[Andrew Poulsom]

Yes if you have the origin, destination and a list of places with their time differences from GMT.

 

[jstarr]

I have a spreadsheet with the time zones and their difference from the GMT.

 

[Andrew Poulsom]

So what have you tried?

 

[jstarr]

I added a column next to the local time for GMT. I tried to do Local Time subtracted from a VLookup for the Airport Code. The Vlookup is working, but for some reason, the rest of that formula isn't.

 

[Andrew Poulsom]

The rest of what formula? Can you post some sample data?