Changing displayed time to reflect a time zone difference 1

[st5en]

I work for a company with worldwide contacts and would like to add the time to a form that displays the current date and time for a specific time zone in relation to ours.

I have a field listing the timezones of the companies we're working with in offset to GMT. I am in Arizona, which is sometimes GMT-7, and sometimes GMT-6 (Arizona doesn't do daylight savings).

So if someone is calling Australia +11, I need to adjust the time -8 hours to me, but it needs to adjust the date to tomorrow.

This is made even more difficult by the lack of daylight savings. I need this field to adjust to daylight savings times with a date function as well.

 

[TheWildHare]

Try setting your time values and using the DATEDIFF function to add/subtract. It should work - esp. if you remember to declare your variables as DATE/TIME

 

[st5en]

Okay, I've tried playing with datediff, but I can't get it to work properly. So far, I can only get the time to change.

This is what I'm using:
=Format(Time()+[Time],"Medium Time&quot

I'm sure this would be easier in VB, but I don't think I know enough to get this to work.

My variable [Time] is set up as DATE/TIME difference from GMT. I guess what I want to do is combime Date and Time into one variable, adjust it, then convert it back to Date & Time.

The last thing, through working on this: Is there a way to input negative time in a DATE/TIME field?

 

[TheWildHare]

The last thing, through working on this: Is there a way to input negative time in a DATE/TIME field?

If you find a way, be sure to let Einstein and Hawking know....

Seriously, though, NO. A Date/Time object is used to store an exact "moment in time" - not a "difference" between two times or dates. This is a common mistake that many people make and it digs their hole even deeper.

Let's say you have a variable, "TimeDiff" - this holds the NUMBER of hours difference between the subject time and GMT - it could be positive or negative. It should be defined as a NUMBER type though, not a DATE/TIME type.

Then your math merely subtracts the TIMEDIFF guy from the GMT guy (which IS a date/time) to get another NUMBER guy:

First, you need to initialize GMT, to whatever it really is, right now..to do that in your system, you'll need to know how far ahead GMT is from your local time..Let's assume you're on EDT like me - GMT is +5

GMT = DateAdd("h", 5, Now() )

Then you use DATEDIFF with the 'h' (hours) modifier to find the difference in HOURS between your TimeGuy and GMT.

TimeAdjust = DateDiff("h", TimeGuy, GMT)

The key here is making sure you set GMT correctly from your "base" time zone.

Just remember that the difference between two date/time guys is a NUMBER (of somethings - sec, min, hour, day, week, month, years, etc etc etc) , not another date/time guy.

Jim

 

[st5en]

Thank you, that is a fantastic explanation of how that works. I will work on this some more, but with your help I think I should be able to get it to work.

Thanks again,
Steve