Eventsforce

When running reports in Eventsforce to show date and time (ex: payment date/time, registration date/time), Eventsforce always displays this information in UTC (Coordinated Universal Time) time zone. UTC is also used in all backend areas of Eventsforce. 

Clients who are running events in other time zones may want to display their date/time in the local time zone. This can be done by manually editing the exported report from Eventsforce and adding a new column with an excel formula.

Note: This article uses to Microsoft Excel as the program in which the report is exported and modified.

Add a custom time zone column to an excel report

In order to display a custom time zone, event managers must make the updates within the Microsoft Excel platform by taking the steps below:

First export your report to an Excel/CSV file (ensure you have a column to show date/time)

Right click on the column after the "Date/time" column and select "Insert" to add a new row

Give your row a name (ex: Payment Date/time (PST))

Add the following formula into the first row of the column, where "I2" is the original date/time cell, and "8/24" references the hours out of 24 you need to remove from the UTC time zone (ex: PST = UTC - 8 hours):

Click "Enter" after adding the formula and the data should automatically display. If the date/time does not display automatically, you may need to "Format cells" and choose the 'Date' display option that includes date/time.

Use the small square icon in the bottom right of the cell and drag this formula to all rows below to populate data for the entire report

1. First export your report to an Excel/CSV file (ensure you have a column to show date/time)
2. Right click on the column after the "Date/time" column and select "Insert" to add a new row
3. Give your row a name (ex: Payment Date/time (PST))
4. Add the following formula into the first row of the column, where "I2" is the original date/time cell, and "8/24" references the hours out of 24 you need to remove from the UTC time zone (ex: PST = UTC - 8 hours): 
 =SUM(I2-(8/24)) 
 Example:

5. Click "Enter" after adding the formula and the data should automatically display. If the date/time does not display automatically, you may need to "Format cells" and choose the 'Date' display option that includes date/time.
6. Use the small square icon in the bottom right of the cell and drag this formula to all rows below to populate data for the entire report

7. Save the report

Formulas for various time zones (be sure to update "I2" to reflect the cell of the date/time in UTC), be sure to take into account 'daylight savings time':

Note: The "I2" value should be replaced with the letter for the column the UTC date/time appears in, as well as the cell number the date/time value appears in.

<a href="https://help.eventsforce.net/en/articles/2272181-how-to-create-a-report" target="_blank" rel="nofollow noopener noreferrer">How to create a report</a>

<a href="https://help.eventsforce.net/en/articles/2274779-how-to-edit-a-report" target="_blank" rel="nofollow noopener noreferrer">How to edit a report</a>

<a href="https://help.eventsforce.net/en/articles/2287370-how-to-schedule-a-report" target="_blank" rel="nofollow noopener noreferrer">How to schedule a report</a>

- <a href="https://help.eventsforce.net/en/articles/2272181-how-to-create-a-report" target="_blank" rel="nofollow noopener noreferrer">How to create a report</a>
- <a href="https://help.eventsforce.net/en/articles/2274779-how-to-edit-a-report" target="_blank" rel="nofollow noopener noreferrer">How to edit a report</a>
- <a href="https://help.eventsforce.net/en/articles/2287370-how-to-schedule-a-report" target="_blank" rel="nofollow noopener noreferrer">How to schedule a report</a>

Time Zone	Excel Formula
Eastern Standard Time	=SUM(I2-(5/24))
Central Standard Time	=SUM(I2-(6/24))
Mountain Time	=SUM(I2-(7/24))
Pacific Standard Time	=SUM(I2-(8/24))
Beiging, Hong Kong	=SUM(I2+(8/24))

Add a custom time zone column to an excel report

Tips

Other articles you may find useful: