# excel formula to convert time zone

• ### Question

• Hi,

I have time in the format as : - "10:01:00 AM" in an excel data cell. But it is in the CST format, I need to convert it to IST format which is 11 hours 30 minutes ahead of CST.

How can i do this please ?

Thank you !

Tuesday, December 15, 2015 11:17 AM

• Two ways:

1. Current time + TIME(hours difference from UTC,0,0), for ex.: A1+ TIME(3,0,0)

2. Current time + (hours difference from UTC/24)

• Marked as answer by Thursday, December 17, 2015 11:25 AM
Tuesday, December 15, 2015 1:41 PM
• Re:  change time

11.5 divided by 24 = 0.479167

For the above to work, Excel must recognize the entry as a date, not as text.

'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs  (xl97 to xl2010 only)
http://jmp.sh/K95N3ee
• Marked as answer by Thursday, December 17, 2015 11:25 AM
Tuesday, December 15, 2015 5:45 PM

### All replies

• Hi,

The excel cell has data as: " 10/12/2015  12:50:41 AM " in CST time zone. I need to convert it to IST time zone by adding 11 hours and 30 minutes. How to do that ?

Thank you !

Tuesday, December 15, 2015 11:57 AM
• Hi,
In my excel extract i see the date time in the CST time zone, and i would like to convert it into another time zone that is 11 hours and 30 minutes ahead of CST. How can i do that please ?

I have data as: 10/12/2015  12:50:41 AM

Thank you !

Tuesday, December 15, 2015 12:03 PM
• Two ways:

1. Current time + TIME(hours difference from UTC,0,0), for ex.: A1+ TIME(3,0,0)

2. Current time + (hours difference from UTC/24)

• Marked as answer by Thursday, December 17, 2015 11:25 AM
Tuesday, December 15, 2015 1:41 PM
• Re:  change time

11.5 divided by 24 = 0.479167

For the above to work, Excel must recognize the entry as a date, not as text.

'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs  (xl97 to xl2010 only)
http://jmp.sh/K95N3ee
• Marked as answer by Thursday, December 17, 2015 11:25 AM
Tuesday, December 15, 2015 5:45 PM
• Thank you for the responses.
Thursday, December 17, 2015 11:25 AM
• You need to keep track of Date as well. This formula will do the needful.

Note: Adjust the value -TIMEVALUE("9.30") accordingly.

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

-- Sanjeev Kumar

Friday, August 18, 2017 5:14 AM