How do I convert the time in my transactions report to my time zone?

Your reports show the time and date as year-month-day and in 24 hour format for UTC (Universal Time Coordinated). UTC is also known as Greenwich Mean Time. If you want to know what time something in your event happened in your time zone, you'll need to convert it, but you don't have to do it yourself. Make Excel do the heavy lifting for you. 

For this example, I'm using the created on transaction date from column AJ of the transactions download. The process is the same for any report, but the column label may be different.

1. Open your report, select column AJ, and right click.
2. Click Format Cells and format all of the cells in that column as date or time that looks like 3/14/12 13:30.
3. Next, insert a column to the right of your created on column (AJ). This will create column AK.

Paste the correct formula from the list below into the the first cell below the header of row AK (the column you just created). This is cell AK2. In order for this to work for you, you'll need to know your time zone and how many hours to subtract or add from UTC. Here's what that formula looks like for our most common time zones:

Eastern

=TEXT(DATEVALUE(AJ2)-TIME(5,0,0)+TIMEVALUE(RIGHT(AJ2,8)),"yyyy-mm-dd hh:mm:ss")

Central

=TEXT(DATEVALUE(AJ2)-TIME(6,0,0)+TIMEVALUE(RIGHT(AJ2,8)),"yyyy-mm-dd hh:mm:ss")

Mountain

=TEXT(DATEVALUE(AJ2)-TIME(7,0,0)+TIMEVALUE(RIGHT(AJ2,8)),"yyyy-mm-dd hh:mm:ss")

Pacific

=TEXT(DATEVALUE(AJ2)-TIME(8,0,0)+TIMEVALUE(RIGHT(AJ2,8)),"yyyy-mm-dd hh:mm:ss")

Hawaii

=TEXT(DATEVALUE(AJ2)-TIME(10,0,0)+TIMEVALUE(RIGHT(AJ2,8)),"yyyy-mm-dd hh:mm:ss")

Finally, copy the formula down to the rest of the cells in your spreadsheet so the entire row is done.