Calculating Time Zone Differences

I haven't worked with FM for some time and I can't find this answer in the books/manuals I have....

I have a lookup field "Time Zone" that finds and displays the time zone name ("Eastern", "Central", etc.) based on area code.  It works fine.  

What I now need is a way to use "Time Zone"  to calculate what the current  time is in, say, "Pacific" if I'm in "Eastern" and show Pacific local time in a field called "Time Zone Calc".  IOW, if the current time is 5:29pm EST then "Time Zone Calc" should say "2:29pm".
Who is Participating?
FM 7 does have the Get(CurrentTime) function. There is no space, maybe that's why you're having trouble?

In my opinion, the best option is to provide a time zone field associated with each individual contact, and populate this with a default value based on either the zip code or the state. You can then go in and manually correct any exceptions.

Here's how a calculated field would work. Create a field with each contact called LocalTimeOffset, and populate it with the difference between your local time and that of the area you're calling. So if Bob is 3 hours earlier than you, you would put -3 in that field. Then you have a calculated field that adds the time difference times 3600 (the number of seconds in an hour) to your local time. It would look something like this:

  RemoteTime = Get(CurrentTime) + LocalTimeOffset * 3600

When you download a zip code database, the latitude and longitude information is an approximation of the center of the zip code region, and isn't really relevant to area codes. You should not need to use latitude/longitude info for this particular function.

The problem is that you're dealing with three different ways of dividing up the country into zones. Area codes are based on telephone company networks phone company networks and equipment. Zip codes are based on the truck routes taken by postal workers delivering the mail. And time zones are based on a combination of political and geographical boundaries.

It's not always practical to assign a single time zone to an area code, because they can overlap. For example, the 850 area code is for northwest Florida, including the panhandle. But half of this area code is in central time and the other half is eastern time. Zip codes are more popular for this purpose because they're much smaller areas, so any discrepancies will be a lot smaller.

If you don't want to deal with the complexity of zip codes, another quick and dirty approach would be to base your time zones on the state. There are 13 states that have more than one time zone, and most of these have one predominant time zone for most of the state, with a much smaller part of the state in another time zone. Here's a list of the split states.
 North Dakota
 South Dakota

You will have to establish your time zone relative to GMT (Grenich mean time) then use get(current time) to get your local time. Convert it to GMT. Then based on area code or time zone recompute the time in that zone. For example PST is GMT-8 hours and Eastern is GMT-5 hours
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

I do have a zipcode db and every zip code has a field where I store the time difference to my zip.
E.g. In zip 92506 it's 5:40 pm then in 10003 which is NY zipcode I store a +3, so where ever I need to process the time all I do is add +3  to 5:40pm and it tells me the NY time.

The reason to do theis by zip is that there are certain areas in the US that have different time zones like AZ.
Jvaldes' suggestion is the correct way to handle this. Convert the local time to GMT, then convert to the remote time from that. It's the most flexible and error-resistant method.

A quick-and-dirty alternative is to just add another field to your time zone lookup table and store the local time difference in that field. Then use a relationship based on the time zone name or ID, something like this:

calculated field Remote_Time
  = Local_Time - Areacodedata::TimeZoneDiff

But don't do this, it's a bad idea. Go with Jvaldes' suggestion.
Ok, and how do you know what is the time for X place unless you store the place and the time zone?
If you don't store it, then you'll need to enter it every time (enter place and GMT difference).

Phaeberle, that is the intention. Trishadee has a database of locations with time zones.
trishahdeeAuthor Commented:
So I'm stumped on how to implement jvaldes soloution.  I don't have a get(current time) function.  I'm using FM7 and that may be a function of a newer version.  

Also, to get around the "split area code" problem, I just downloaded a csv file with "ZIP","CITY","STATE","COUNTY","LATITUDE","LONGITUDE","AREACODE" that I could use to make things more precise but the lat/long is a whole other problem.

Where do I go from here?

BTW, thanks for working with me!
trishahdeeAuthor Commented:
Just wanted you to know that I am still working on your suggestions and haven't got to a point where I can update you yet.  I'll let you know when I know if it worked...
trishahdeeAuthor Commented:
billmercer, it worked!

You were right about everything.  I decided that I wanted to get as accurate as possible.  So I bought a reasonably priced database ($25) that included "ZIP Code", "State", "City", "County", "Area Code", "Time Zone", and "Daylight Savings".

Then I followed your instructions using the Time Zone instead of the Area Code which took care of the split-state and split-area code problem.

Thank you very much for working with me!!
Glad to hear you were able to resolve it!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.