What formula can convert the date and time zone in Excel??

[coffeefirst]

Hello!

I'm having trouble applying a formula (I tried the formulas listed here) to two columns, one column 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 also needing to be adjusted.
If the date column was "E", what would that formula look like?

Any thoughts?
Many thanks in advance.

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[coffeefirst]

Here is a segment of my excel spreadsheet looks like with the formula that isn't working:
Excel Time zone question.JPG

[Pete_UK]

I think you probably want this:

=C3 + D3 - TIME(7,0,0)

Use a Custom Format to see the date and time in the style your prefer.

Hope this helps.

Pete

[coffeefirst]

Pete, I'm not religious, but bless your heart! That did the trick!
I appreciate your help tremendously!

[Pete_UK]

Ah well, it's good to hear that.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete