The first day of the fiscal year can change over time, no? So this formula would need to be maintained or altered over time? And can projects also span through multiple fiscal years? So you’d sometimes end up with FY04, FY05?
The first day of the fiscal year can change over time, no? So this formula would need to be maintained or altered over time? And can projects also span through multiple fiscal years? So you’d sometimes end up with FY04, FY05?
No, the fiscal year stays the same. Always Dec 1 through Nov 30. And this isn’t for project span, but publication date / end of project, so if something is published on December 2, 2003 it would always be FY04.
No, the fiscal year stays the same. Always Dec 1 through Nov 30. And this isn’t for project span, but publication date / end of project, so if something is published on December 2, 2003 it would always be FY04.
Ok, if I’m getting this right, then if a date is between Dec 1 and Jan 1, we’ll return the following calendar year, otherwise we’ll return the current calendar year.
IF(
AND(
IS_AFTER(
{Published Date},
DATESTR("" & YEAR({Published Date}) & "-11-30" )
),
IS_BEFORE(
{Published Date},
DATESTR("" & YEAR({Published Date}) + 1 & "-01-01" )
)
),
"FY" & RIGHT(""&(YEAR({Published Date}) + 1),2),
"FY" & RIGHT(""&YEAR({Published Date}),2)
)

Ok, if I’m getting this right, then if a date is between Dec 1 and Jan 1, we’ll return the following calendar year, otherwise we’ll return the current calendar year.
IF(
AND(
IS_AFTER(
{Published Date},
DATESTR("" & YEAR({Published Date}) & "-11-30" )
),
IS_BEFORE(
{Published Date},
DATESTR("" & YEAR({Published Date}) + 1 & "-01-01" )
)
),
"FY" & RIGHT(""&(YEAR({Published Date}) + 1),2),
"FY" & RIGHT(""&YEAR({Published Date}),2)
)

Ahh, yes, this is such a smart way to do it and I feel stupid, thank you.
One thing: the first row, 12/1/2019 should be FY20 because Dec 1 is the first day the next FY. But the rest of the rows are correct. So should I change the top date to “-11-30”?
Ahh, yes, this is such a smart way to do it and I feel stupid, thank you.
One thing: the first row, 12/1/2019 should be FY20 because Dec 1 is the first day the next FY. But the rest of the rows are correct. So should I change the top date to “-11-30”?
Definitely not stupid. This is a tricky one with all the formatting and type conversions.
should I change the top date to “-11-30”?
Yeah I believe so. It’s yours now. Feel free to mess around with it.