Calculatinig timezone clock times

coolgaff · May 10th, 2002, 07:12 AM

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.

Corticus · May 10th, 2002, 07:16 AM

=+A1-9/24

Say A1 is your original time. -9/24 will give you 9 hours earlier. Substitue the 9 for whatever time difference you need, or substitute a variable for variable difference.
Make sure to use date and time format.
Corticus

coolgaff · May 10th, 2002, 08:06 AM

Many Thanks, this is what I need. Medical calls to 9 different countries at 10,000+ where the patient has specified the time is challenging....

thanks

coolgaff · May 10th, 2002, 08:09 AM

Hi following up on this, what would you advise for when the time goes back a day say call time is 08:00am which -9/24 is 23:00 excel just shows ######??

Corticus · May 10th, 2002, 08:11 AM

make the column bigger

Corticus · May 10th, 2002, 08:11 AM

or the text smaller

Corticus

coolgaff · May 10th, 2002, 08:20 AM

HI I have tried both no joy
formula reads in cell e16, =+D16-9/24 but gives ########
d16 is 08:00 if I change d16 to anything higher (09:00) then it converts the time no problem as it recognises it as in the same day.

Corticus · May 10th, 2002, 11:40 AM

okay,

since I started the D16 thing,
D16(your date/time input cell) should be entered in the form of a date and time. Make sure that Excel recognizes it as such.
First, format D16 as "3/14/98 1:30 pm" under the date format
then format E16 (or wherever you put the formula) the same
then do:
=+D16+H/24
where H = the number of hours +/- for going forward or back,
it works on mine!
-Corticus

Jack in the UK · May 10th, 2002, 01:47 PM

suggest userform and UDF to add or less hours required can add drop down to select the country as UDF will comvert.

silly idea why not have command button to open userform with 9 clocks on the digital..

erm be careful as the macro will run and excel will not allow other useage... but close can kill the code..

some suggestions.

I once had Java clock which moved.... secondes and so on...

even had my name rotating arroung the clock round fact...

coolgaff · May 13th, 2002, 01:55 AM

Hi I must be a muppet still cannot get it to work.
sample below it now gives the name error

Call Date "Initial
Call Time (24hr clock) "
Time Actual UK timezone +hrs
11-Jun 19:00 #NAME? 9
22-Jun 18:30 #NAME? 9
11-Jun 16:00 #NAME? 9
11-Jun 19:00 #NAME? 9

May 10th, 2002, 01:47 PM	#9
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	suggest userform and UDF to add or less hours required can add drop down to select the country as UDF will comvert. silly idea why not have command button to open userform with 9 clocks on the digital.. erm be careful as the macro will run and excel will not allow other useage... but close can kill the code.. some suggestions. I once had Java clock which moved.... secondes and so on... even had my name rotating arroung the clock round fact... __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 10th, 2002, 07:12 AM	#1
coolgaff New Member Join Date: May 2002 Posts: 7	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.

May 10th, 2002, 07:16 AM	#2
Corticus Board Regular Join Date: Apr 2002 Location: Sarasota, FL Posts: 1,539	=+A1-9/24 Say A1 is your original time. -9/24 will give you 9 hours earlier. Substitue the 9 for whatever time difference you need, or substitute a variable for variable difference. Make sure to use date and time format. Corticus

May 10th, 2002, 08:06 AM	#3
coolgaff New Member Join Date: May 2002 Posts: 7	Many Thanks, this is what I need. Medical calls to 9 different countries at 10,000+ where the patient has specified the time is challenging.... thanks

May 10th, 2002, 08:09 AM	#4
coolgaff New Member Join Date: May 2002 Posts: 7	Hi following up on this, what would you advise for when the time goes back a day say call time is 08:00am which -9/24 is 23:00 excel just shows ######??

May 10th, 2002, 08:11 AM	#5
Corticus Board Regular Join Date: Apr 2002 Location: Sarasota, FL Posts: 1,539	make the column bigger

May 10th, 2002, 08:20 AM	#7
coolgaff New Member Join Date: May 2002 Posts: 7	HI I have tried both no joy formula reads in cell e16, =+D16-9/24 but gives ######## d16 is 08:00 if I change d16 to anything higher (09:00) then it converts the time no problem as it recognises it as in the same day.

May 10th, 2002, 11:40 AM	#8
Corticus Board Regular Join Date: Apr 2002 Location: Sarasota, FL Posts: 1,539	okay, since I started the D16 thing, D16(your date/time input cell) should be entered in the form of a date and time. Make sure that Excel recognizes it as such. First, format D16 as "3/14/98 1:30 pm" under the date format then format E16 (or wherever you put the formula) the same then do: =+D16+H/24 where H = the number of hours +/- for going forward or back, it works on mine! -Corticus

May 13th, 2002, 01:55 AM	#10
coolgaff New Member Join Date: May 2002 Posts: 7	Hi I must be a muppet still cannot get it to work. sample below it now gives the name error Call Date "Initial Call Time (24hr clock) " Time Actual UK timezone +hrs 11-Jun 19:00 #NAME? 9 22-Jun 18:30 #NAME? 9 11-Jun 16:00 #NAME? 9 11-Jun 19:00 #NAME? 9