Problems exporting tables to excel

[JamesParker4]

When I export a table to excel, it changes the times of the entries.  It had been adding 10 hours, I'm guessing to make it compliant with UTC, but now it switched and is subtracting 12 hours.  Date/Time stamp on AGOL is correct when I view it in the Data View Tab, but I can't manipulate it there, so I have to export it to Excel.  When I do that, it always changes the times.  Is there a setting I can change?  What's the reason its doing this?

[DouglasCochran]

When a date field is displayed in ArcGIS Online, the date is converted from UTC to your local time.This is done by querying your computer and asking it for its time zone setting. Can you confirm your computer time is correct? Below is more info on working with date fields in ArcGIS Online.

Work with date fields—ArcGIS Online Help | ArcGIS 

If you still have problems, can you provide the excel file so we can test?

[JamesParker4]

This is how it looks on AGOL. The data is collected using Collector on a tablet set to our current time zone(HST).  

Here is the same dataset on Excel:

So it is converting it to UTC when it exports to Excel, but AGOL is storing it as HST.  

Thanks for looking into this.  

[DouglasCochran]

When you import the excel spreadsheet back to ArcGIS Online, does it convert it back to HST with the correct time?

[JamesParker4]

Yes

[JamesParker4]

I still don't have a solution to this problem.  I need to record the data in local time and I need a way to export the data to excel in local time.  

If that is not possible then I need to export to another file format that I can copy and paste from.  I wouldn't need to export anything at all if I could just copy and paste from the data view inside of AGOL but is that possible?  

[KellyGerrow]

Hi James,

ArcGIS Online stores data in UTC, and displays data in the local timezone on your machine. Check out this blog for more information about how we handle time on the web. By doing this, we have a standard way of allowing users in various time zones to enter time and have it standard across time zones. When you export to excel, it exports the data as it is stored in the database in ArcGIS Online which is UTC. If you are using any ArcGIS Online applications, they will act in the same way, in that date and time will be added in the local computer time and transformed to UTC during the edits.

How are you trying to manipulate the data? Typing in the time manually? 

You should be able to manually edit the data without having to think about the time transformation.

Is the date field part of editor tracking?

Are you looking to use the sported CSV for a specific purpose or is it to correct issues in the data?

When publishing a CSV you can select the timezone depending on the format the time is collected in. If its in local time, specify the timezone when you publish, if you know the data is in UTC, then no action is required when you publish. IT will still appear in your computer's local time when you view it in ArcGIS Online.

Check out the blog and let us know if it helps.

-Kelly

[JamesParker4]

Wow, Mastering the space/time continuum was not on my To-Do list for today.  All I need to do is take some of the data from AGOL and input it to a Google Sheet for reporting.  I don't do any editing beyond that.  .CSV had the same issue.  

I tried publishing the data, choosing the Hawaii time, and it came out correct on AGOL.  But when I exported that again, the times were all off.  

Is there no Desktop collector app that I can examine the data within and do my cut/paste from?  

I can edit the data in AGOL, but I can't select several fields from a column and cut/paste.  

I appreciate the help.

[KellyGerrow]

I can't believe master the space-time continuum is not on your to do list for the day.

Thanks for the extra information. I'm going to send you an email so we can chat about some additional specifics to see what can get you up and running. Will post back once I know a little more.

Thanks,

Kelly

[KellyGerrow · Accepted solution]

Hi James,

As we discussed, ArcGIS Online keeps all time date fields in UTC. This is so it can be properly transformed and stored in a standard format that will appear correct in the web. As you are exporting this document to be used without an application that transforms the time from UTC to your local time, you will need to transform the time manually using tools available to you.

In excel, you can use the time function to specify the conversion from UTC. In the attached csv file, I converted the exported UTC time to HST by removing 10 hours from UTC. The formula is 

=b2-TIME(10,0,0)

 or

=cellvalue -TIME(10,0,0)

This will be the most effective way to resolve the issue for now, but thanks for providing the feedback about improving the experience with copy and pasting of values to dig into further.

As another option, consider using crowdsource manager to display your data that makes date/time transformations to local time zones automatically.

Crowdsource Manager Crowdsource Manager | ArcGIS Solutions for Local Government 

https://www.arcgis.com/home/item.html?id=43a4a0dbf9914f93bf0657f7839fa655 

http://webapps.maps.arcgis.com/apps/CrowdsourceManager/index.html?appid=71ebab73929f4168b444df88b80c... 

Thanks,

Kelly