Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Calculatinig timezone clock times

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    Board Regular Corticus's Avatar
    Join Date
    Apr 2002
    Location
    Sarasota, FL
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =+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. #3
    New Member
    Join Date
    May 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    New Member
    Join Date
    May 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    Board Regular Corticus's Avatar
    Join Date
    Apr 2002
    Location
    Sarasota, FL
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    make the column bigger

  6. #6
    Board Regular Corticus's Avatar
    Join Date
    Apr 2002
    Location
    Sarasota, FL
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    or the text smaller

    Corticus

  7. #7
    New Member
    Join Date
    May 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #8
    Board Regular Corticus's Avatar
    Join Date
    Apr 2002
    Location
    Sarasota, FL
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #9
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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"

  10. #10
    New Member
    Join Date
    May 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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