Converting UST columns to PST timezone

Posted on 2008-10-18
Last Modified: 2012-06-21
I need to convert this UTC timestamp to PST:

5/29/2008 2:06:00 PM
so it reads:
5/29/2008 6:06:00 AM

Assuming this requires an expression, Im not sure how to code this.


Question by:Cole100
LVL 33

Expert Comment

ID: 22747550
Use the DateAdd function like this:


this is for adding 4 hours to your field value.


Expert Comment

ID: 22747567
wouldnt it be -4 as you are taking away  ?

Author Comment

ID: 22747583

I need this to convert the whole columns data.....not just a single field.  My report is generated from a linked db and updates daily. I want to build an expression that 1. displays DATE()-1 and  2. converts the same UST columns to PST. Pardon my confusion.

Author Comment

ID: 22747613
UST - 8hrs = PST, I thought?
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

LVL 49

Expert Comment

by:Gustav Brock
ID: 22747711
Here is a generic solution.

Call it like this:

datUTC = #5/29/2008 2:06:00 PM#
lngBiasUTC = 0
lngBiasPST = 60 * 8
datPST = DateAddTimeZoneDiff(datUTC, lngBiasUTC, lngBiasPST)

Public Function DateAddTimeZoneDiff( _

  ByVal datLocal As Date, _

  ByVal lngLocalBias, _

  ByVal lngRemoteBias) _

  As Date


' Calculates the date/time of datLocal in a remote time zone.

' Adds the difference in minutes between the local time zone bias and

' the remote time zone bias, if both bias are relative to UTC.


' Examples:


'   datRemote = DateAddTimeZoneDiff(Now(), 60, -600)

' will return datRemote as eleven hours ahead of local time.


'   datRemote = DateAddTimeZoneDiff(Now(), -600, 60)

' will return datRemote as eleven hours behind local time.


' 2000-05-30. Cactus Data ApS, CPH.

' 2005-01-25. Adjusted to accommodate change in TimeZoneBiasDiff().

  Dim datRemote As Date

  Dim lngBias   As Long


  ' Get difference (in minutes) in time zone bias.

  lngBias = TimeZoneBiasDiff(lngLocalBias, lngRemoteBias)

  ' Calculate remote date/time.

  datRemote = DateAdd("n", lngBias, datLocal)


  DateAddTimeZoneDiff = datRemote


End Function

Public Function TimeZoneBiasDiff( _

  ByVal lngLocalTimeBias As Long, _

  ByVal lngRemoteTimeBias As Long) _

  As Long


' Calculates the difference in minutes between two time zones,

' typically from the local time zone to the remote time zone.

' Both time zones must be expressed by their bias relative to

' UTC (Coordinated Universal Time) which is measured in minutes.


' 2000-05-30. Cactus Data ApS, CPH.

' 2005-01-25. Output reversed to express bias value.

  ' Minimum amount of minutes for a time zone bias.

  Const clngTimeZoneBiasMin As Long = 15

  Dim lngTimeZoneBiasDiff As Long


  ' Round off time zone bias by minimum time zone difference.

  lngLocalTimeBias = lngLocalTimeBias \ clngTimeZoneBiasMin

  lngLocalTimeBias = lngLocalTimeBias * clngTimeZoneBiasMin

  lngRemoteTimeBias = lngRemoteTimeBias \ clngTimeZoneBiasMin

  lngRemoteTimeBias = lngRemoteTimeBias * clngTimeZoneBiasMin


  ' Calculate difference in time zone bias.

  lngTimeZoneBiasDiff = lngLocalTimeBias - lngRemoteTimeBias


  TimeZoneBiasDiff = lngTimeZoneBiasDiff

End Function

Open in new window


Author Comment

ID: 22749507
I dont want to put in a specific datetime..........I need all the fields under the timestamp column to be converted to PST.


Author Comment

ID: 22749566
specifically:  I need all fields to convert for cloockin datetime and clockout datetime.

                                                           Clock_In_Datetime      Clock_Out_Datetime      EOD Clockout Flag

      Last name      First name      10/17/2008 5:00:00 PM      10/17/2008 9:43:00 PM                  0
      Last name      First name      10/17/2008 10:25:00 PM      10/18/2008 1:11:00 AM                 -1
LVL 49

Accepted Solution

Gustav Brock earned 500 total points
ID: 22750049
Well, then create a query where you use the function:

  DateAddTimeZoneDiff([Clock_In_Datetime], 0, 480) As Clock_In_Datetime_PST,
  DateAddTimeZoneDiff([Clock_Out_Datetime], 0, 480) As Clock_Out_Datetime_PST


Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now