Using numbers mixed with fields in the Formula Tool

[aalbera]

•  FY START (Data Type: Date)
  • '2020-07-01' 
• FY End (Data Type: Date)
  • '2021-06-30' 
• FY (Data Type: Float)

  • IF [FY Start]<[Service Thru]<[FY End]
    THEN DateTimeYear([FY End])

    ELSEIF [FY Start]-365<[Service Thru]<[FY End]-365
    THEN DateTimeYear([FY End])-1

    ELSEIF [FY Start]-(365*2)<[Service Thru]<[FY End]-(365*2)
    THEN DateTimeYear([FY End])-2

    ELSEIF [FY Start]-(365*3)<[Service Thru]<[FY End]-(365*3)
    THEN DateTimeYear([FY End])-3

    ELSEIF [FY Start]-(365*4)<[Service Thru]<[FY End]-(365*4)
    THEN DateTimeYear([FY End])-4

    ELSEIF [FY Start]-(365*5)<[Service Thru]<[FY End]-(365*5)
    THEN DateTimeYear([FY End])-5

    ELSE [FY End]-6

    ENDIF

I am trying to create a field that returns the fiscal year that a transaction posted in [FY] by running a nested IF function in the Formula Tool using the FY Start and FY End fields as the base. In doing this, I am trying to use actual integers in the expression box separated by a subtraction and multiplication operator, but it doesn't appear to be picking them up. Is there a specific way that I should be noting the integers here, or an entirely better way to approach this formula?

[PhilipMannering]

Perhaps you could start with something like,

datetimeyear([FY End]) - datetimediff([FY End], [Service Thru], 'years')

And see how far that gets you. 

[aalbera]

That is definitely helpful! I am still getting a Parse Error (Malformed If Statement, Expression #3) here. 

IF [FY Start]<[Service Thru]<[FY End]

THEN DateTimeYear([FY End])

ELSE DateTimeYear([FY End])-DateTimeDiff([FY END],[Service Thru],'Years')

ENDIF

[kelsey_kincaid]

Hi @aalbera ,

Try this syntax and see if this works for you.

IF [FY Start]<[Service Thru] and [FY Start]>[FY End]
THEN DateTimeYear([FY End])
ELSE DateTimeYear([FY End])-DateTimeDiff([FY END],[Service Thru],'Years')
ENDIF