Thanks:  0
Likes:  0

# Thread: Calculatinig timezone clock times

1. 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.

2. =+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

3. 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

4. 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 ######??

5. make the column bigger

6. or the text smaller

Corticus

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.

8. 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

9. 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...

10. 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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•