Skip to main content

Hi everyone, 

I am trying to create a formula that will output what funding year a record falls within. I have a field with the dateStart of the funding and another with the dateEnd of the funding. I also have a program start date which is a static field. I am calculating the funding year manually and am sure there is a formula that could do this for me, but I haven't been able to figure it out. In the example below here is how I get the funding year: 

I write out the date ranges and manually tag the funding year depending on what year it falls within. The program start date is what the funding years depend on.

9/1/2019-8/31/2020 = Y1

9/1/2020-8/31/2021 = Y2

9/1/2021-8/31/2022 = Y3

9/1/2022-8/31/2023 = Y4

Any suggestions would be MUCH appreciated!

Your example shows multiple funding years, but you mentioned that the program start date is what the funding years depend on. If it is supposed to be just one year based on the start date, you could use this to determine funding year:

IF(
IS_BEFORE(dateStart, DATETIME_PARSE("9-1-2019")),
"Error",
IF(
IS_BEFORE(dateStart, DATETIME_PARSE("9-1-2020")),
"Y1",
IF(
IS_BEFORE(dateStart, DATETIME_PARSE("9-1-2021")),
"Y2",
IF(
IS_BEFORE(dateStart, DATETIME_PARSE("9-1-2022")),
"Y3",
"Y4"
)
)
)
)



Your example shows multiple funding years, but you mentioned that the program start date is what the funding years depend on. If it is supposed to be just one year based on the start date, you could use this to determine funding year:

IF(
IS_BEFORE(dateStart, DATETIME_PARSE("9-1-2019")),
"Error",
IF(
IS_BEFORE(dateStart, DATETIME_PARSE("9-1-2020")),
"Y1",
IF(
IS_BEFORE(dateStart, DATETIME_PARSE("9-1-2021")),
"Y2",
IF(
IS_BEFORE(dateStart, DATETIME_PARSE("9-1-2022")),
"Y3",
"Y4"
)
)
)
)




Hi Ron, 

Thank you so much for your response. Your formula works, but only for the very specific example I provided (see screenshot below). The program entry date is static and pulled via a lookup field but it is not always going to be 9/1/2019. 

And yes, some records (funding records) can fall in multiple funding years. 

Would it work to replace the dates in the formula with the dateStart and compare it to the programEntryDate/

 

It appears I'm not understanding your use case. Some clarifying questions:
• Are the funding years a specific date range? (For example, does your fiscal year start Sept 1?)
• Is Year 1, Year 2, etc. referring to how many year(s) that record was funded in, or specific years? (i.e., Does Year 1 mean 2019, or does it mean the first year that record was funded?)
• What is the difference between Program Entry Date and Start Date?

Reply


Terms & Conditions