Help creating a GMT/Local time spreadsheet

[talonpilot]

Hello everyone, its been a while since my last post but after searching the boards for hours, I give up and need to ask for some help. Thanks to all who have posted and answered, its always a great help! I am sorry if I dont fully explain myself, I may be a little "punch drunk" fm searching for so long..... here is my delima.....

I have created a spreadsheet where I enter the date and time into cell A3 (formatted as dd-mmm-yy, hh:mm). In cell A4, i have created a formula that uses a vlookup to search "table1" for the local time zone conversion factor. My formula reads "=A3-VLOOKUP(A1,Table1,4,FALSE). My formula works great, as long as you subtract the vlookup value fm A3. As I travel across timezones, eventually I will need to add time to A3.

How can I write this formula to return the local time, weather it is GMT -7:00 or GMT +4:00, without going in and actually changing the formula to read "=A3+VLOOKUP(A1,Table1,4,FALSE)? I need it to search the table to find out weather it should add 7 hrs, or subtract 8 hrs (depending on the timezone I am in). Is there a better way to create table1 so that when it looks it up, it will "just know" to add/subtract x hrs fm the time entered?

Currently I am still a little unsure about which time format to use when entering the station time (i.e. New York GMT -4:00; do I use hh:mm, [h]:mm, general, etc?), but I want the dispayed results to be displayed juat as I entered the original time (dd-mmm-yy, hh:mm).

ANY help to get me headed in the right direction would be great. Thanks again for the posts!

Mark

 

[Andrew Poulsom]

You can't display negative times in Excel unless you use the 1904 date system, which will corrupt any dates currently in your workbook. You can enter a negative time like this:

-"07:00"

and format the cell as General. Then you need only add your VLOOKUP result.

 

[talonpilot]

Andrew,

Thanks for your reply, sorry that it took me so long to thank you for your suggestion. It worked perfectly! It wasnt quite what I had in mind, but it works very reliably for what I am doing. Thanks again!!

Mark