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

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"

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

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

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

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)

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.