Timezone difference

Hi all,
I'm struggling to work out how to show how many hours difference there is between 2 timezones, especially when they are on different days.
I'm in the UK so Sydney is 11 hours ahead of me (which is tomorrow) but excel only seems to want to count backwards for this time.
Does anyone know how i can fix this?

Cell D26 is set as =NOW() formatted hh:mm:ss
Cell D29 pulls another timezone from a formula also formatted hh:mm:ss

I would like D33 to show the difference in time such as +5:00 or -5:00.

Thanks in advance

If you have want to know how the difference between 00:30 and 23:30, then you would use something like:
=24+"00:30"-"23:30"
but how would excel know whether to count forwards or backwards?

Thank Norie,
but i'm still getting the output of -998635.93

Even with =IF(D26>D29,D26-D29,D26-D29)*24 i'm still getting -19 when it should be -5

Cell D26 is =now()
Cell D20 is has the time in the chosen City

Thank newdoverman but when I tried that formula it gives an output of 0.791666667 when I'm looking for -5

At Jimmisavage, you need to format D33 to time and that should change the decimals into hours.
I think 24+D29-D26 would give you the correct answer for the difference between two times when one is the day after the next.

However, the bit I still don't get if you have:
21:30 in D26
and
04:30 in D29

Then how do you know if it is 04:30 the next day or 04:30 the previous day, somewhere you need to tell it whether the time is ahead or behind.

Hi yudlugar,
If I format the cells the date and time it understands that its a day ahead but i don't want the date to show on my spreadsheet. Does that help at all?

I think an example workbook might help here. Do you mean that the values in the cells are date&time values that you have formatted to show only a time?

In which case I would expect the other solutions that are posted would work, although I believe
=abs(D26-D29)
would also work. You just need to format D33 to time

Hi sorry for the delay - here is an attachment: Office Address and Timezones.xls

Now a perfect example is when I search for COL (buy typing COL into cell D13) it should say -5 hours but instead it gives me -14:18??

It's fair to say I'm pretty stumped right now! Any help would be appreciated.

Thanks again

Hi - your attachment works fine, the formula in F22 is correct.

The problem is that some of your times in column E on the office sheet are hard typed, so are not related to the current time.

For example, COL has a time of 19:00:00 hard typed. If you change this so that it is the correct time relative to the current system time then it will give you the correct answer..