How to convert time in a CSV file using Excel

When you download a CSV file with time stamps, the time in the file is always UTC. Raw data on our servers are stored in UTC, and this data is not converted when the CSV is downloaded. 

In this example, we're using a CSV file downloaded from Monitor.

If you use Excel, there is a quick and simple way to change the time to your current time zone by using a formula that adds or subtracts hours based on the UTC time.

• The formula looks like this if you want to add 10 hours
  • =<cell name>+(10/24)
    
    
• If you want to subtract 4 hours, the formula looks like this
  • =<cell name>-(4/24)

Import the CSV file to Excel

1. Open a new Excel document and navigate to the Data tab
2. Click From Text
3. Navigate to the CSV file and click Import

Your excel file will look like this:

Add or subtract hours to columns with time stamp

In this example, we're adding 10 hours to lastSaved, and we start with the first cell in the column - F2.

1. Create a new column.
2. Add the formula =F2+(10/24) in the first cell in the new column. In the video below, this is G2.
3. Hit enter.

As you can see in the video, this should automatically populate the entire column with the new time value.

Automate by creating a macro

This is possible to automate by recording a macro in Excel. Microsoft has several resources on how to record macros, and to start with you need to add the Developer tab to Excel if it's not already there.

How to add the Developer tab.

How to record a macro.