I would like to have a formula where I can input a date and time from one time zone (HKT, GMT, etc...) and have the conversion time be my local time (Central Time Zone). I've tried doing this but one problem I'm having is when I put Tuesday, July 29th @ 10:00 AM HKT and convert that to my local time, it would be Monday, July 28th @ 9:00 PM CDT.

I'm struggling building a formula that would recognize that the time would be 9 PM of the previous day. Does anyone have an answer for this?

I want to be able to plug in dates and times for HKT, GMT, EDT and have it convert to my local time.

I would like to have a formula where I can input a date and time from
one time zone (HKT, GMT, etc...) and have the conversion time be my
local time (Central Time Zone [CDT]).

Suppose an HKT date/time is in A1 as an Excel "date & time" value, for example:
Tuesday, July 29 @ 10:00:00 AM
using the custom format
Dddd, Mmmm dd ""@"" h:mm:ss AM/PM"

In B1, put the formula
=A1-13/24
and using the same format get
Monday, July 28 @ 9:00:00 PM

Explanation:

Referring to
http://www.timeanddate.com/library/a...ons/timezones/
the difference between HKT and CDT is 13 hours; that is 13/24 days.

Since daylight savings time isn't universal, the formula should change over the year. For example, U.S. Central Time uses DST but Hong Kong does not. To be thorough, you would have to keep track of time standards in the places of interest to you.

Hope this helps.

Thank you for the quick reply.

This however isn't exactly what I'm looking for. In A1, I need to input not just HKT but also GMT, EDT, etc... I need a formula that will convert the time back to my local time no matter what I put into that cell. I was thinking of using nested IF functions and just subtracting the time based on A1 = the date, A2 = the time and A3 = the time zone.

Maybe there's a way to do it with combining date and time in the same cell. I will continue to explore but let me know if there's another way using this parameters.

Thanks

I have figured out a formula and then formatted the cells appropriately to get them the way I need them to look. I couldn't upload the document but attached are two screenshots of the values and the corresponding formulas.

Thanks to MyVeryOwnSelf for showing me the 13/24. Couldn't have figured it out with that!

hi Jeff,

Am Fri, 25 Jul 2014 14:16:43 +0100 schrieb Jeff Kohl:

I have figured out a formula and then formatted the cells appropriately
to get them the way I need them to look. I couldn't upload the document
but attached are two screenshots of the values and the corresponding
formulas.

in A1 try:

=B1-TIME(VLOOKUP(C1,{"HKT",13;"EDT",1;"GMT",5},2,0),,)
Possibly you have to modify the separators

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional