Trying to use a calculated field to determine which Fiscal Year the Planned Start Date is in

[ChaseAd1]

Hi,

I have been working on a calculated field that grabs the Planned Start Date from a form and then converts it to an abbreviated version of the fiscal year that date is in. Here's my code:

IF(DATE({plannedStartDate})>=DATE(9/1/2023) && DATE({plannedStartDate})<=DATE(8/31/2024), "FY24", "") ||
IF(DATE({plannedStartDate})>=DATE(9/1/2024) && DATE({plannedStartDate})<=DATE(8/31/2025), "FY25", "") ||
IF(DATE({plannedStartDate})>=DATE(9/1/2025) && DATE({plannedStartDate})<=DATE(8/31/2026), "FY26", "") ||
IF(DATE({plannedStartDate})>=DATE(9/1/2026) && DATE({plannedStartDate})<=DATE(8/31/2027), "FY27", "") ||
IF(DATE({plannedStartDate})>=DATE(9/1/2027) && DATE({plannedStartDate})<=DATE(8/31/2028), "FY28", "") ||
IF(DATE({plannedStartDate})>=DATE(9/1/2028) && DATE({plannedStartDate})<=DATE(8/31/2029), "FY29", "")

When I do this, the field just shows "false". What am I missing here? 

Thank you to everyone who responds!

[Sven-iX · Accepted solution]

Well the structure you have with || means the expression will return TRUE or FALSE only... Instead, you need to nest the IFs.
{plannedStartDate} is already a date, no need to convert.
Try something like this

IF({plannedStartDate}>=DATE(9/1/2023) && {plannedStartDate}<=DATE(8/31/2024), "FY24",
IF({plannedStartDate}>=DATE(9/1/2024) && {plannedStartDate}<=DATE(8/31/2025), "FY25",
IF({plannedStartDate}>=DATE(9/1/2025) && {plannedStartDate}<=DATE(8/31/2026), "FY26", ""))) 

I *think* what your logic says is that 

• If Month is greater than 8, it's currentYear + 1
• otherwise, it's currentYear 

"FY" + RIGHT(IF(MONTH({plannedStartDate})>8, YEAR({plannedStartDate})+1, YEAR({plannedStartDate})),2)

[ChaseAd1]

"FY" + RIGHT(IF(MONTH({plannedStartDate})>8, YEAR({plannedStartDate})+1, YEAR({plannedStartDate})),2)

This is perfect, thank you! I'm still new to coding in Workfront, so I didn't realize the OR expression was returning true or false.

Thank you so much for your help!

[Sven-iX]

Glad to have you!