Import Problem (Date Format): Excel to AGOL (hosted feature layer)

234
1
09-14-2021 01:31 AM
Tobias
by
New Contributor II

Hi all, 

I'm trying to update a feature layer with an excel sheet. However I can't get the date to be working properly: 

- When date is just the plain date (without time) in the Excel sheet -> Output (Dashboard):  entered date with a time stamp - which is wrong. I'd like it to show "14.09.2021 16:00" for example. 

- Date format in Excel is "Date" + a text "16:00" (custom format) -> AGOL does not recognize the additional text. Quite obvious, actually. Excel format does not add to the value itself.

- Date in the sheet is combined as:  =TEXT(today()-1; "DD.MM.JJJJ")&TEXT(L2;" HH:MM:SS") 

This should give me yesterdays date (as text) + the time from field L2 (as text) -> "13.09.2021 14:00:00"

This gives me exactly what I need inside the spreadsheet hoewever AGOL does not recognize the additional time stamp. 

- When the date is set to just a value of "14.09.2021  16:00" AGOL does recognize the date _and_ the time, but the spreadsheet is no longer comfortable to use because of all the manual date changes. 

 

Is there a way to format Excel dates to be recognized by AGOL? 

Or alternatively: Is there a way to shut off the time-extention when using the date on a dashboard? Right now, when I'm using the field "Date", the result is: "14.09.2021  02:00:00"  (2 AM due to UTC time shift)

 

Best regards

Tobias

0 Kudos
1 Reply
KatyShea
New Contributor

Tobias,

Did you have any luck with this?  I see your post is several months old with 0 replies.  I am having a similar problem, though it is in the opposite direction.  I am exporting a layer out of AGOL to Excel and found that the dates on my original layer were not formatted the same.  I tried to clean it up and reformat the dates to the American standard like 01-31-22.  When I add a new entry (date), it auto-formats it back to 31-01-22.  I have NO IDEA how many dates could be wrong now in my layer due to this flip-flop.  

0 Kudos