Conversion of Time Zones in an Excel Work sheet

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 w开发者_JAVA技巧ant 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.

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.

You'll have to setup some cells that have the DST offsets in them, and them Sum or Subtract them from the CST time.