Conversion of Time Zones in an Excel Work sheet

Question

I have a dialer report containing date and time of calls dialed to locations in different timezones. The start time field in the report for the dialed calls reflects in CST zone. I want to convert this CST time into other time zones as per locations to where calls were dialed. So how do I calculate the CST equivalent time for other locations in the excel sheet.

Solution

I had a similar problem, and perhaps my solution will help you. I have columns with dates and times expressed in IST and I want to convert to EDT. So I am using this formula:

=DATEVALUE(TEXT(B42,"MM/DD/YYYY"))+TIMEVALUE(TEXT(C42, "HH:MM:SS AM/PM"))-TIMEVALUE("9:30")

(where B42 is a date, C42 is a time, and 9.5 hours back is the conversion from IST to EDT.
Of course you have to change to 10:30 (10.5 hours back) when we roll to EST. And for yours, since there is a master date and time cell (the CST cell), you probably want to use an absolute cell reference.