topic Re: Convert 5 digit date serial number to MM/DD/YYYY in Data Management Questions

Convert 5 digit date serial number to MM/DD/YYYY

PhilLampe1 — Fri, 01 Apr 2016 19:28:21 GMT

Hello,

I have a string field (named StartDate) in an attribute table that contains numerous 5 digit (post 1900, Excel style) values (such as 40,240) that I need to convert into into a readable date format (which in this case would be MM/DD/YYYY).

I have created an empty field named StartDateDT to house the converted serial dates. The field is in DATE format.

What is the code in VB (preferred) or python to do this?

I am using ArcMap 10.22, standard (aka ArcEditor) license, BTW.

Many THANKS in advance!

Phil in Houston

Re: Convert 5 digit date serial number to MM/DD/YYYY

ChrisDonohue__GISP — Fri, 01 Apr 2016 19:54:42 GMT

Can you post a sample of what your data looks like? I know you described it above, but at least at first glance I'm not seeing how 40,240 turns into a date, specifically what date should that be after correct conversion to MM/DD/YYYY?. Or if you know it, what is the formula to do the conversion? Given that, it shouldn't be too hard to code a conversion so the new date field can be populated.

Chris Donohue, GISP

Re: Convert 5 digit date serial number to MM/DD/YYYY

PhilLampe1 — Fri, 01 Apr 2016 21:24:21 GMT

Chris,

I made one small error when I posted the example of the data value 40,240. It should be w/o the comma. So it would actually be 40240. This is a number that comes from within Excel and represents a serial number that Excel uses to create a date after the year 1900. It is not a Julian date, but from what I gather is commonly referred to as an Excel serial date.

I've attached a screenshot showing the two columns in question.

So the goal here is to take the 5-digit values in Col-A and convert them into a readable date format in Col-B.

As for what formula to use, I am not sure where to begin on that. Sorry.

I hope this helps to clarify some.

Re: Convert 5 digit date serial number to MM/DD/YYYY

DanPatterson_Retired — Fri, 01 Apr 2016 21:28:44 GMT

This has some relevant information

https://support.microsoft.com/en-us/kb/214099

for example

SUMMARY

This article explains how to convert serial number dates to "Julian dates" and how to convert Julian dates to serial number dates in Microsoft Excel 2000. Julian dates refer to the number of days from the first of the year and the number of days until the end of the year as printed on most desktop calendars.

Many government agencies and contractors require the use of Julian dates.

Examples of Julian Dates

The following are examples of Julian dates:

Date Julian date -------------------------

Jan-01-1999 99001

Jan-10-1999 99010

back to the top

Date Conversion Formulas

The following examples show how to work with serial number dates and Julian dates.

To Convert a Serial Number Date to a Julian Date

Use the following Microsoft Excel 2000 formula to convert a serial number date to a Julian date

=TEXT(Standard_Date,"yy")&TEXT((Standard_Date- DATEVALUE("1/1/"&TEXT(Standard_Date,"yy"))+1),"000")

Re: Convert 5 digit date serial number to MM/DD/YYYY

PhilLampe1 — Fri, 01 Apr 2016 22:16:10 GMT

Dan,

Thanks for passing the handy reference for converting the conversions from Julian to Serial, etc. In my initial Google searches I also came across this information as I was in the belief that I was looking for a conversion from Julian to a standard date format. But what I then realized is that I'm actually trying to convert a Serial date to calendar date.

The Julian date is not exclusive to Excel but the Serial date is - as you my know already, and goes almost to the beginning of recorded history. In contrast the Excel Serial date begins in the year 1900 (or 1904 in some cases).

The formulas provided in the MS bulletin will work in Excel but I don't think they'll suffice in ArcMap as I believe ArcMap doesn't always follow Excel's reserved words in VB syntax, right?

My dilemma is how do I find the comparative code in VB for ArcMap to use in the field calculator.

Does this make sense?

Oh, and I forgot to mention - if there's any way to do this using Python I'm open to suggestions.

Cheers

Re: Convert 5 digit date serial number to MM/DD/YYYY

VinceAngelo — Sat, 02 Apr 2016 03:04:14 GMT

There are many potential routes from "days since 01-Jan-1900" to a proper timestamp. One of the easiest might be to subtract 25569 (the Excel value of 01-Jan-1970), multiply that day value by 86400 (60*60*24 seconds per day), then use "seconds since 01-Jan-1970" to make a conventional date type.

- V

Re: Convert 5 digit date serial number to MM/DD/YYYY

PhilLampe1 — Sat, 02 Apr 2016 17:13:25 GMT

UPDATE: I got a reply (which I consider a working solution) from another posting I put up on StackExchange. The responder (Midavalo) provided this Python code to be put in the field calculator box:

datetime.datetime(1899, 12, 30) + datetime.timedelta(days= !INSERT FIELD NAME HERE! )

The StackExchange posting can be found here: http://gis.stackexchange.com/questions/187695/convert-5-digit-serial-date-to-mm-dd-yyyy/187722#187722

Many THANKS to Chris, Dan and Vince for their inputs here at Geonet.

BTW, would this have been better posted in another of the Geonet's sub-groups like Python? I must say I find the new forum interface both confusing and hard to figure out where to post something. Is there any type of document(s) that can provide a better insight on how to find where you want to post something here at the Geonet site?

Cheers!

Re: Convert 5 digit date serial number to MM/DD/YYYY

DanPatterson_Retired — Sat, 02 Apr 2016 18:50:01 GMT

No, Managing Data is fine since it is really just about a data format conversion and not specific to Python or Geoprocessing.

Re: Convert 5 digit date serial number to MM/DD/YYYY

deleted-user-8TJ6jZDO1Rfr — Wed, 03 Apr 2019 21:52:06 GMT

Dealt with this issue today. Your code worked after we told it to view the string field as an integer.

This is the code we used in the Field Calculator:

datetime.datetime(1899,12,30) + datetime.timedelta(days = int( !START_DATE!))

Re: Convert 5 digit date serial number to MM/DD/YYYY

kennethf — Wed, 16 Jun 2021 19:18:03 GMT

Hi Vince,

I hope you're still out there after all these years. I need the solution you suggest but I don't know how to finish the equation.

My sample input serial date is 44360

My equation is (44360 - 25569) * 86400

which returns 19-Jan-1970

Can you help?

Thanks,

Ken

Re: Convert 5 digit date serial number to MM/DD/YYYY

VinceAngelo — Thu, 17 Jun 2021 14:39:58 GMT

18791 days after 01-Jan-1970 isn't likely to be 19-Jan-1970; perhaps you used a converter based on milliseconds, and you need to multiply by an additional 1000?

- V

Re: Convert 5 digit date serial number to MM/DD/YYYY

kennethf — Thu, 17 Jun 2021 15:30:52 GMT

Thanks Vince!