Trying to use a calculated field to determine which Fiscal Year the Planned Start Date is in | Community
Skip to main content
C
ChaseAd1
March 19, 2025
Solved

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

  • March 19, 2025
  • 1 reply
  • 836 views

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!

    Best answer by Sven-iX

    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)

     

    1 reply

    Sven-iX
    Community Advisor
    Sven-iXCommunity AdvisorAccepted solution
    Community Advisor
    March 19, 2025

    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)

     

      C
      ChaseAd1Author
      March 19, 2025
      "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
        Community Advisor
        Sven-iXCommunity Advisor
        Community Advisor
        March 19, 2025

        Glad to have you! 

          Terms & ConditionsAccessibility statement

          Loading footer...