display time in different time zone

[Guest]

I have list of different time in column A. I would like to display the times in Column B in a different time zone which is 11 hours delayed than the corresponding times in Column A. Would any one help?

 

[Peo Sjoblom]

One way

=MOD(A1-11/24,1)

format as time and copy down

--

Regards,

Peo Sjoblom

I have list of different time in column A. I would like to display the

times in Column B in a different time zone which is 11 hours delayed than
the corresponding times in Column A. Would any one help?

 

[Chip Pearson]

James,

You can use a formula in column B like

=TIME(HOUR(A1)+11,MINUTE(A1),SECOND(A1))
or more simply but less explanatory
=A1+(11/24)
or
=A1+0.458333333333333

If by "delayed" you mean "earlier" rather than "later", change
the '+' signs to '-' in all the above formulas.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

I have list of different time in column A. I would like to

display the times in Column B in a different time zone which is
11 hours delayed than the corresponding times in Column A. Would
any one help?

 

[Guest]

Thank

It works well

james

 

[Guest]

Thanks

It works well.

 

[David McRitchie]

Hi James,
I think you would do much better if you included the date and time in column A
=NOW() so that you seen the date and time in Column B. Hit F9 to update.

I have list of different time in column A. I would like to display the times in Column B in a different time zone which is 11

hours delayed than the corresponding times in Column A. Would any one help?

 

[JohnT]

Chip

Using the formula that you supplied below, I get the correct result
Even over midnight.

However, if I change it to -11, I get the correct result UNLESS it goe
back past midnight. When I go back past midnight I get #NUM.

What am I missing????

=TIME(HOUR(A1)+11,MINUTE(A1),SECOND(A1))

Regards
John
PS Love your page

 

[David McRitchie]

Hi JohnT,
To fix Chip's formula you might use something like
you have to add 1 day (24 hours if time < 0)

A1: 2004-05-17 10:07:00 PM from =NOW()
will display according to your regional settings.

B1: =IF(TIME(HOUR(A1)+11,MINUTE(A1),SECOND(A1))<0,
1+TIME(HOUR(A1)+11,MINUTE(A1),SECOND(A1)),
TIME(HOUR(A1)+11,MINUTE(A1),SECOND(A1)))`

If you used my suggestion, you can make a table, Update with PF9

B1: - 11/24 C1 - 10/24 D1: -9/24
A2: =NOW()
B2 =$A2 + B$1
C2: =$A2 + C$1
D2: =$A2 + D$1

 

[JohnT]

David

I must be doing something blatantly wrong here. Even with your formula
I STILL get either #NUM or ########### when going back past midnight

Joh

 

[David McRitchie]

Hi John,
Are the cells formatted properly for a time or for a datetimestamp
and are the cells wide enough. I've sent you an email with an example
in an HTML table.

Format as hh:mm if just a time
Format as yyyy-mm-dd for a timestamp (or your datetime format)

Format as # ?/24 for adding hours as fraction of a day like 1/24 or -3/24 .

 

[JohnT]

David

All the Cells are formatted correctly as hh:mm.

=MOD(A1-11/24,1)

Works OK. For 08:00 today it gives me 21:00 yesterday

=TIME(HOUR(A1)-11,MINUTE(A1),SECOND(A1))

Doesn't work. For 08:00 today it gives me #NUM!
It DOES OK work for going forward past midnight.

I can use the first formula, but I would like to know why your formul
is not working. Things like this dig away at me until I slowly g
mad!!!!!

Joh

 

[David McRitchie]

I'm a dummy the one that works is Peo's though it will not tell you what
day it is that could mess you up. Mine gave you dates and time.

But the one you have a problem with is Chip Pearson's, but didn't
the minor (twice as long) correction that I made make that work but
it only returns time and Peo's is a better solution for that.

 

[JohnT]

David

Yuor formula of

=IF(TIME(HOUR(A1)-11,MINUTE(A1),SECOND(A1))<0,
1+TIME(HOUR(A1)-11,MINUTE(A1),SECOND(A1)),
TIME(HOUR(A1)-11,MINUTE(A1),SECOND(A1)))

Still gives me #NUM! if I try to go BACk through midnight

The dent in the wall near my desk is getting bigger and the bruise o
my forehead is getting darker

Joh

 

[David McRitchie]

Ok thought Time could be negative if you formatted it as number
but =TIME(-4,0,0) results in #NUM! so I can't fix Chip's formula
with all that extra code so use Peo's or use date.

And mine is not good either with the date, because there is no
reference to UT so you don't know where the international
date line is (supposed to be) anyway. So use Peo's. and forget
about the date.

 

[JohnT]

David

I'm kinda glad it wouldn't work for you too. I've organised a plastere
to fix the wall and the bruise is slowly going down.

Thanx for all the help

Joh