Litigation Support

Litigation Support - Tool Tips - Calculate Time Zone Offset Using Excel

By Ann Halkett posted 11 days ago


There may be times when you need to adjust the time zone for a data set. For example, where you process the data in Coordinated Universal Time UTC (aka Greenwich Mean Time or GMT), but the action itself is in Pacific Standard Time (PST).  To do this, you can create a Time Zone Offset field (or several of these depending on the nature of your case) to display the date/time information for that particular time zone.  Note that you cannot change this information on the document itself, ONLY in a field.  Also note that this will not take daylight savings time into account.  (However, you could do additional calculations if you determine when daylight savings time occurred.)

To do this, look to a field that contains both the date and time information.  You may need to do some work to combine the information into one field (e.g. Email Sent Date/Time and Last Modified Date/Time). If using Relativity however, you may have a field which is auto populated with this information when you process the records (i.e. Primary Date/Time). 

Example - End Result:

How To:

1. In an empty cell type the following formula where you want to display the offset Time Zone date and time information:

F2 is the column/cell containing the date and time you want to convert
-(minus) for deduct or + (plus) for add
7 is the number of hours difference that we want to deduct (E.g. PST is -7 from UTC)
24 indicates that there are 24 hours in a day

2. Drag the fill handle for the above cell down to populate the cells below with the new date and time information.

3. You may need to format the cells so that the information displays properly.  Highlight the column and right-click and choose Format Cells… Set as per the screen shot below and choose OK.

4. You will need to copy the contents from the cells to make the information permanent as the cells only contains a formula.  Create a new column to the right and highlight the column you want copy and choose Ctrl + C and then highlight the column where you want to place the results and then right-click and choose the following under Paste Options .

5. IF you want the date format to remain the same, i.e. M/DD/YYYY hh:mm:ss then under Step 3 above choose Custom and then populate the Type as per the screen shot below:

If starting off in litigation support learn Excel.  You can do a LOT with it!

1 comment