Time Zone Conversion

[vinsavant]

I have a dataset that collects downtime minutes of internet service by an internet provider. This provider is on the U.S. East coast EST and has customers in England, India and Hong Kong.



I need to create some type of query, calculated field, filter or parameter that allows the user after filtering to show events for their country to determine if the downtime event happened before or after their business hours, for example, 08:00 AM and 17:00 PM in the customer's time zone.

Thank you for your help!

[ranman256]

I use a time zone converter. Each city,country has a timezone code, and the difference in UTC.
google this table to get a full list.

TimeZone	desc	UTC
AKST	Alaska Standard Time (AKST)	-9
ATC	Atlantic Standard Time (ATC)	-4
CHST	Chamorro Standard Time (CHST) +10	10
CST	Central Standard Time (CST)	-6
EST	Eastern Standard Time (EST)	-5

[vinsavant]

Hi Ranman,

Thank you for your reply. Can you describe how you coded this? I need all the help you can provide me.
Thanks!

[June7]

The date/time is stored as the provider's EST?

Need a table of customers with field for their TimeZone code. Query would include the data table, the customers table, and the timezone table. Calculation to convert from provider's EST to customer's local would be like:

DateAdd("h", 5 + [UTC], [fieldname])

However, that doesn't account for daylight savings switch which apparently India and Hong Kong don't observe so half of the year would require an additional hour adjustment for those locales, also the U.S. and England don't switch on same dates. This makes the conversion calculation complicated enough a VBA custom function may be needed.