topic Re: Converting numeric time values into date format in ArcGIS Pro Questions

Converting numeric time values into date format

AustinReeves — Tue, 17 Dec 2019 03:20:22 GMT

Hello,

I have run into a little problem regarding the formatting of dates within a ArcPro table. First off, the table is originally a .xlsx and has been converted using the "excel to table" tool. The original excel table has a date field with values in the format of "12/23/1998 14:40" and has upon using the tool, has been converted into the 5-digit numeric date scheme. These numeric dates have decimals i.e. "26507.6875" and is due to what I assume to be the time of day. I have found this thread Date field in Excel to Table tool , but upon trying the code I get the standard "999999" message (unexpected error). Any other suggestions on how I can get the dates back to the regular format? Thanks!

-Austin

Re: Converting numeric time values into date format

DanPatterson_Retired — Tue, 17 Dec 2019 11:52:28 GMT

You should probably show your expression, especially to check on how you truncated your time from the string since the example in your thread was for dates, not datetime

Re: Converting numeric time values into date format

JoeBorgione — Tue, 17 Dec 2019 13:42:39 GMT

What Dan said. In the thread you reference I apply int() to a string. Is your 'decimal day' numeric or string?

Re: Converting numeric time values into date format

AustinReeves — Tue, 17 Dec 2019 20:56:17 GMT

The expression I used was: datetime.datetime(1899,12,30) + datetime.timedelta(days = int(!Sample_Date_Time!))

My field containing the dates is the "Sample_Date_Time" and the rest was derived from that thread I linked. I'm not too familiar with python yet so would not be too sure where or what to change about this code.

Re: Converting numeric time values into date format

AustinReeves — Tue, 17 Dec 2019 20:58:59 GMT

By 'decimal day' you are referring to the format that is being created in the ArcPro table (26507.6875) correct? I would assume this is a string as it contains decimals.

Re: Converting numeric time values into date format

JoeBorgione — Tue, 17 Dec 2019 21:35:45 GMT

26507.6875 could be string; could be a float; could be a double.....

Re: Converting numeric time values into date format

AustinReeves — Tue, 17 Dec 2019 21:44:17 GMT

Alright, I have looked in the fields editor and it is listed as a text data type, with the number format as numeric. This is what the original date format is being converted to via the "excel to table" tool. Thanks again for the help!

Re: Converting numeric time values into date format

JoeBorgione — Tue, 17 Dec 2019 21:52:26 GMT

As I recall, when the excel table runs in an excel date field, as long as all of the fields have values it will bring them over as date fields. But... if any of those excel fields are empty, it converts the whole lot of them into the Microsoft date index and types the field as text.

Re: Converting numeric time values into date format

AustinReeves — Tue, 17 Dec 2019 21:56:14 GMT

That is correct in that the excel table I'm working from has missing values for various rows. However, it would be quite tedious to sort through all 80,000 and something samples ha! I wonder of there's an excel function to weed these out...

Re: Converting numeric time values into date format

AustinReeves — Tue, 17 Dec 2019 22:00:44 GMT

Have now filtered out all blanks and will see how this new table converts to ArcPro!

Re: Converting numeric time values into date format

JoeBorgione — Tue, 17 Dec 2019 22:25:49 GMT

I get what you're saying; I was converting a couple thousand records and more or less stumbled on this particular 'un-documented feature'. It's also when I first learned of the Microsoft date indexes described in my other thread you reference above.

Re: Converting numeric time values into date format

DanPatterson_Retired — Tue, 17 Dec 2019 22:48:13 GMT

from

Work with Microsoft Excel files in ArcGIS Pro—Excel | ArcGIS Desktop

If you have cells with numeric data, dates, and so on, ensure that the content is consistently formatted—in other words, make sure all numeric data is actually numeric. If there are other types of data in those rows, the field is converted to text when the table is opened in ArcGIS Pro.

They fail to mention that "blanks" can break the "consistently" rule.

No cell should go empty!! There should be a huge warning flash onscreen when blanks are encountered

If you are working in Pro tables from scratch....

/blogs/dan_patterson/2019/11/28/the-solution-to-null-in-tables

Or know some python

/blogs/dan_patterson/2018/12/13/excel-arrays-tables-in-arcgis-pro

Re: Converting numeric time values into date format

JoeBorgione — Tue, 17 Dec 2019 22:53:21 GMT

I love this : No cell should go empty!!

Couldn't agree more, but we know what reality gives us!

Re: Converting numeric time values into date format

AustinReeves — Wed, 18 Dec 2019 02:49:34 GMT

Dan Patterson and Joe Borgione, I got the new excel worksheet created with no blanks (plus the date range I'm interested in), and it transferred over to ArcPro with no issues! Wanted to say thanks again, as I wouldn't have known where to go without both of your guys' input!

-Austin‌