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

**mailtojsb** · 01-24-2008, 07:40 AM

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

**oldchippy** · 01-24-2008, 07:46 AM

Hi,

Here's a link that may help?

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

**daddylonglegs** · 01-24-2008, 08:04 AM

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"

**mailtojsb** · 01-24-2008, 09:07 AM

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

**oldchippy** · 01-24-2008, 09:20 AM

Hi,

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

**daddylonglegs** · 01-24-2008, 09:40 AM

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

**coffeefirst** · 08-05-2016, 08:08 PM

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.

**alansidman** · 08-05-2016, 08:35 PM

@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.