Closed Thread
Results 1 to 10 of 10

How to do Date + Time Zone conversion in Excel??

  1. #1
    Registered User
    Join Date
    01-24-2008
    Posts
    2

    How to do Date + Time Zone conversion in Excel??

    How to do Date + Time Zone conversion in Excel??

    Hi Experts, I am seeking help to understand the way of doing Date/Time Zone conversion from one excel column to other column.

    In brief, Say I have a Date/Time value in mm/dd/yyyy HH:MM format in column A1 which is actually a PST Time, now I need a formula to convert it to IST format in column B1 but the Date/Time format should remain same i.e. mm/dd/yyyy HH:MM

    I am not at all good in Excel so thought of seeking some assistance here. I feel itís a complicated task and if anyone can help meÖit will be great and I will be thankful from the bottom of my heart.

    Thanks again for sparing time to read my query.

    Cheers
    Jsb

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Hi,

    Here's a link that may help?

    http://www.exceltip.com/st/Convertin...other/834.html
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,674
    IST? Is that Indian standard Time? If so then you need to add 13:30 hours to convert PST to IST.In which case you can use this formula in B1

    =A1+"13:30"

    format B1 the same way as A1

    Note: you may have to do adjustments for "daylight saving"

  4. #4
    Registered User
    Join Date
    01-24-2008
    Posts
    2

    How to do Date + Time Zone conversion in Excel??

    Guys thanks for your swift response but let me explain my requirement in a better way.

    Daddy n oldchippy;The link or formula you suggested only converts the time but I need to change the date as well. The format of the column A1 is mm/dd/yyyy HH:MM. So I need a formula which converts the time Zone including date as well.

    For example, when 01/24/2008 03:00PM (PST) gets converted to IST (Indian Standard Time) then it should give a value like: 01/25/2008 4:30AM.
    This is an exact conversion I am looking for coz Date gets change as well when time changes while converting PST <---> IST.

    I hope you would have understood my needs better from this example. See the below snap shot to have a look at the cells format.

    http://img66.imageshack.us/my.php?image=snap3zb4.png

    Cheers
    jsb

  5. #5
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    Hi,

    You need a Custom format like mm/dd/yy hh:mm AM/PM

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,674
    The formula I suggested will change the date as appropriate e.g. if the time is after 10:30 AM then the date will be some time in the next day. As per my post (and as oldchippy says) this should just be a case of formatting the result cell to show date and time as you wish

  7. #7
    Registered User
    Join Date
    11-20-2013
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    1

    Question Re: How to do Date + Time Zone conversion in Excel??

    How can I parameterise the time change? - I am preparing a schedule of when I want a task to be performed by a global shared service centre, but I want the time in local time zone to be one variable, and the time-zone shift to our Global shared service centre to be a second variable so I can then copy the formula down across the 80 off countries I need the task performed for. The example above had the time-zone shift as a fixed formularised shift of +"13:30", whereas I need that to be a parameter/variable/cell reference - the time-zone shift is obviously variable, but I want to flex the local time also so I can fit the whole task into as short a window at our global shared service centre as possible to undertake the transaction outside of local business hours, but minimise inconsistencies arising due to international trades restarting in one country before the other countries have closed.

    Note: I also discovered that if I want the adjustment to be negative (eg converting Shanghai time to GMT (London) is -"08:00" then I have to make the local time variable either greater than 08:00, or merge it with a date parameter, otherwise it assumes the Shanghai time to be the beginning of Excel date function 1 Jan 1900, and gives me an impossible formula - but I solved that one already by adding a random date (other than 1 Jan 1900, of course) into the formula, and formatting the result field as "hh:mm". This was important, as I needed to schedule the task to be done for Shanghai at 7am local time, performed in London at -8hrs!

    The ideal solution, of course, would be for Excel to have a world clock converter (which could also take account of daylight saving adjustments - even better), but no one has prompted that in any of the forums I have looked at, so I presume this doesn't exist. This thread is the most productive I have seen so far, so thanks for the contributions on this theme.

    Thanks

  8. #8
    Registered User
    Join Date
    10-12-2015
    Location
    Indianapolis, IN USA
    MS-Off Ver
    Windows 7 Pro
    Posts
    1

    Re: How to do Date + Time Zone conversion in Excel??

    I just figured this out for myself. Use the SUM function.

    When you do this, select the Date/Time field that you want to convert. Notice that the date displays as an number with 5 numbers after the decimal.
    For example, 9/18/2015 8:00 PM = 42265.83333.
    .83333= 8:00pm

    So if you want to convert to a different time zone, adding 13 hours, 13/24 = .54167; this is the amount you will add using the SUM function.
    For example, =SUM(D2,0.54167)
    Excel will display the adjusted date/time.

    If you wanted to subtract 13 hours, use the following format:
    For example, =SUM(D2,-0.54167)

  9. #9
    Registered User
    Join Date
    08-05-2016
    Location
    San Francisco, CA
    MS-Off Ver
    Microsoft Office 365 2015 version
    Posts
    4

    Re: How to do Date + Time Zone conversion in Excel??

    Hello folks,

    I'm having trouble applying this formula (I tried all the above suggestions) to two columns, one with the date and another with the time. Specifically, I'm trying to adjust the date AND time listed in two columns into a single column with the adjusted times, GMT to California time PST (GMT).
    Is there an adjustment I can work into the formula to adjust the date as well?

    I had some success with this formula:
    (the times were in column F, and this was row 2)
    =F:F F2 - TIME(7, 0, 0)

    However, when the times were adjusted beyond a certain point, I had ##### in my data due to the date needing to be adjusted.
    If the date column was E, what would that formula look like?

    Any thoughts?
    Many thanks in advance.

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    15,061

    Re: How to do Date + Time Zone conversion in Excel??

    @coffeefirst

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1