none
excel formula to convert time zone RRS feed

  • 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

Answers

  • 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 John.P.Smith Thursday, December 17, 2015 11:25 AM
    Tuesday, December 15, 2015 1:41 PM
  • Re:  change time

    11.5 divided by 24 = 0.479167
    Add the above value to your date.

    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 John.P.Smith 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 ?

    Please help.

    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 John.P.Smith Thursday, December 17, 2015 11:25 AM
    Tuesday, December 15, 2015 1:41 PM
  • Re:  change time

    11.5 divided by 24 = 0.479167
    Add the above value to your date.

    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 John.P.Smith 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