Using Model Builder to extract Year/Month from datestamp and calculate financial period

634
2
04-11-2021 07:17 PM
Labels (1)
LindsayRaabe_FPCWA
Regular Contributor

Hi Brains Trust. I'm working on a model and starting to delve deeper into parts I've not really used a lot before - especially not in combination. Here's the scenario. 

I have a feature class that contains a field called DateS which contains date stamps formatted like this: 2020-11-16T00:02:58.096Z

I want to be able to iterate through all the different dates in that column (easy enough using Iterate Field Values), extract the Year and Month (I assume that will be Get Value) and then run a formula to calulate the Australian Financial Year (July - June) that the date range falls in. Example of some output values;

Input from DateS       -      Output as Financial Year (YEAR-Y1)

2020-11-16T00:02:58.096Z       -      2020-21

2020-06-16T00:02:58.096Z       -      2019-20

2019-10-16T00:02:58.096Z       -      2019-20

2019-03-16T00:02:58.096Z       -      2018-19

LindsayRaabe_FPCWA_0-1618193751886.png

 

Lindsay Raabe
Forest Products Commission WA
0 Kudos
2 Replies
DanPatterson
MVP Esteemed Contributor

Calculating Fiscal Year Dates With Python | Coding and Development Blog (paulcunningham.dev) 

which references

adamjstewart/fiscalyear: Utilities for managing the fiscal calendar (github.com)

which leads to the script

fiscalyear/fiscalyear.py at master · adamjstewart/fiscalyear (github.com)

So if you don't want to import the package, you can at least look at the source code to extract the salient parts


... sort of retired...
0 Kudos
JohannesLindner
MVP Regular Contributor

This looks like a simple job for Python or Arcade.

Python:

 

 

# values in Date fields in ArcGis are datetime.datetime objects
import datetime
def get_financial_year(timestamp):
    """Returns the Australian financial year of the given timestamp (as datetime.datetime object) as a string."""
    year = timestamp.year
    if timestamp.month >= 7:
        return "{}-{}".format(year, str(year+1)[-2:])
    return "{}-{}".format(year-1, str(year)[-2:])


# to test it:
timestamps = [
    datetime.datetime(2020, 11, 16, 0, 2, 58),
    datetime.datetime(2020, 6, 16, 0, 2, 58),
    datetime.datetime(2019, 10, 16, 0, 2, 58),
    datetime.datetime(2019, 3, 16, 0, 2, 58),
    ]
for ts in timestamps:
    print(get_financial_year(ts))
#2020-21
#2019-20
#2019-20
#2018-19

 

 

You can use that in the CalculateField tool (so you can also use it as part of a model), or you can just keep working with python:

 

 

fc_path = "path/to/your/feature/class"
date_field = "Timestamp"
financial_year_field = "FinancialYear"

with arcpy.da.UpdateCursor(fc_path, [date_field, financial_year_field]) as cursor:
    for date, year in cursor:
        cursor.updateRow([date, get_financial_year(date)])

 

 


Have a great day!
Johannes