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.