Calculatinig timezone clock times

[coolgaff]

I have a sheet that has entered call times in rows from row 1-1000 (19:00 02:00 etc) these times are in relation to a specific country. What I want to do is have 2 columns/rows that has the time zone difference to UK time (+9hours or - 9 hours etc)what i want to calculate is a formula in the cells next to call time that either deducts or adds the timezone (+/-) from the call time, thus to give actual UK time.

so column A would be local call time 21:00 UK Call time (=local call time minus timezone to give actual time) Timezone +9 can it show the previous date if it goes back a day?

I have consulted my Excel bibles with no joy.

 

[Legacy 98055]

Maybe this will help.<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=5><FONT COLOR=WHITE>Microsoft Excel - Book1</FONT></TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=5>File(<U>F</U>) Edit(<U>E</U>) View(<U>V</U>) Insert(<U>I</U>) Options(<U>O</U>) Tools(<U>T</U>) Data(<U>D</U>) Window(<U>W</U>) Help(<U>H</U>)</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>C3</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=2 BGCOLOR=White>37382.5833333333</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Call Time</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Time Diff +/-</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>Zone Time</FONT></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('37381.75')><FONT FACE=Arial COLOR=#000000>5/5/2002 18:00</FONT></A></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>20</FONT></TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert('37382.5833333333')><FONT FACE=Arial COLOR=#000000>5/6/2002 14:00</FONT></A></TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>=IF(B3>0,A3+TIME(B3,0,0),A3-TIME(ABS(B3),0,0))</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>6</TD><TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM ><FONT FACE=Arial COLOR=#000000>This formula goes into cell C3</FONT></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>7</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD></TR><TR><TD COLSPAN=5><U>Sheet1</U></TD></TR></TABLE><FONT COLOR=#339966>You can see the formula of cells only click each above hyperlinks</FONT><FONT COLOR=#339966 SIZE=1>The above image was automatically written by HtmlMakerVer1.12</FONT><FONT COLOR=#339966 SIZE=1>If you want this code,<A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you. Thanks to Colo for this code.></FONT></CENTER>


Tom
This message was edited by TsTom on 2002-05-13 04:32

 

[coolgaff]

this partially works, apart from the cells where the time difference means the new call time is the previous day, it does not seem to like anything before 9am with a 9 hour difference.

 

[Corticus]

I put this in cell A1 (this really should work):
12/12/02 8:00 AM

in cell B1:
-9

in cell C1:
=+A1+B1/24

Cell C1 displays:
12/11/02 11:00 PM

I can't get this not to work for any time period,

Good Luck!
Corticus

 

[coolgaff]

Hi thanks for the help, it seems to be working. The problem was that the times were already entered and the format for date was sitting at the one with am/pm after the time. Hopefully we can use this to our benefit. Thaks to all great idea for helping and passing the knowledge. anyone advise moving to 97/2000 to office XP!