J
james
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?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Peo Sjoblom
One way
=MOD(A1-11/24,1)
format as time and copy down
--
Regards,
Peo Sjoblom
the corresponding times in Column A. Would any one help?
=MOD(A1-11/24,1)
format as time and copy down
--
Regards,
Peo Sjoblom
times in Column B in a different time zone which is 11 hours delayed thanjames said:I have list of different time in column A. I would like to display the
the corresponding times in Column A. Would any one help?
C
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
11 hours delayed than the corresponding times in Column A. Would
any one help?
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
display the times in Column B in a different time zone which isjames said:I have list of different time in column A. I would like to
11 hours delayed than the corresponding times in Column A. Would
any one help?
J
james
Thank
It works well
james
It works well
james
J
james
Thanks
It works well.
It works well.
D
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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
hours delayed than the corresponding times in Column A. Would any one help?james said: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
J
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
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
D
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
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
J
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
I must be doing something blatantly wrong here. Even with your formula
I STILL get either #NUM or ########### when going back past midnight
Joh
D
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 .
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 .
J
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
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
D
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.
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.
J
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
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
D
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.
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.
J
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
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