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! 

[ashgpt]

Hello, based on the similar query above, is it possible to get the FY# from the following rules?

Rules:
FY24, 25, 26 etc. - If Region is not equal UK or Europe, year end is the end of November (30th of November)

• Ex: between 1st of December 2024 - 30th November 2025, the Fiscal year should be FY25

• Dec 1st 2025 to Nov 30th 2026 = FY26

If Region = UK or Europe,
Jan 1 2025 - Dec 31 2025 = FY25

Jan 1 2026 - Dec 31 2026 = FY26

Thanks in advance.

[Sven-iX]

definitely - MONTH() returns a number 1-12 so you can use the value of a certain field in the "IF" condition and change the >8 to >11

[ashgpt]

Many thanks! That's been done (common sense)

I am not sure what and how to mention in the same syntax 'If Region is not equal UK or Europe' and later ''If Region is equal to UK or Europe'.

IF({plannedStartDate}>=DATE(12/1/2024) && {plannedStartDate}<=DATE(11/30/2025), "FY25",

IF({plannedStartDate}>=DATE(12/1/2025) && {plannedStartDate}<=DATE(11/30/2026), "FY26",

IF({plannedStartDate}>=DATE(12/1/2026) && {plannedStartDate}<=DATE(11/30/2027), "FY27",

IF({plannedStartDate}>=DATE(12/1/2027) && {plannedStartDate}<=DATE(11/30/2028), "FY28",

IF({plannedStartDate}>=DATE(12/1/2028) && {plannedStartDate}<=DATE(11/30/2029), "FY29",

IF({plannedStartDate}>=DATE(12/1/2029) && {plannedStartDate}<=DATE(11/30/2030), "FY30", ""))))))

[Sven-iX]

I dislike hardcoding the dates. 

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

This equates to 

• month of the start date is Nov or Dec then use next year
• otherwise (month is smaller than Nov) use current year 

Regarding  If Region is not equal UK or Europe - well some field denotes what your "region" is, right? If that field e.g. is called DE:region you add that to the IF conditional

"FY" + RIGHT(IF(MONTH({plannedStartDate})>11 && !IN({DE:region}, "UK", "Europe"), YEAR({plannedStartDate})+1, YEAR({plannedStartDate})),2)

Instead of >11 you can arguably use =12 

So 

• If the plannedStartDate is in Dec AND the region is not UK/Europe -> FY = YEAR(plannedStartDate) + 1
• otherwise -> FY = YEAR(plannedStartDate)
  • region is UK or Europe
  • month is anything but December 

[ashgpt]

Amazing! It worked and many thanks @Sven-iX