Calculatinig timezone clock times

coolgaff

New Member
Joined
May 9, 2002
Messages
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
=+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
 
Upvote 0
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 :)
 
Upvote 0
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 ######??
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top