The FY starts June 1, 5/30/2024 should be FY23 but is showing as FY24. What am I doing wrong?

System

This discussion was created from comments split from: Seeking a fiscal year formula :-).

M_Nabulsi

Hi @Paul Newcome, Thank you for providing this formula. I am getting incorrect results. The FY starts June 1, the date 5/30/2024 should be FY23 but is showing as FY24. What am I doing wrong?

="FY" + RIGHT(YEAR(Date@row) + IF(Date@row >= DATE(YEAR(Date@row), 6, 1), 1, 0), 2)

MarceHolzhauzen

• 1. YEAR(Date@row):
  • Extracts the year from the date in Date@row.

• 2. IF(Date@row >= DATE(YEAR(Date@row), 6, 1), 1, 0):
  • Checks if the date is on or after June 1st of the current year (DATE(YEAR(Date@row), 6, 1)).
  • If Date@row is on or after June 1st, the fiscal year will roll over to the next calendar year by adding 1 to the year.
  • Otherwise, the fiscal year remains the same as the current calendar year.

• 3. YEAR(Date@row) + IF(..., 1, 0):
  • Adjusts the year based on the result of the IF statement. If the date is after June 1st, it adds 1 to the year.

• 4. RIGHT(..., 2):
  • Extracts the last two digits of the adjusted year (e.g., for 2024, this will return 24).

• 5. "FY" + ...:
  • Concatenates the string "FY" with the two-digit fiscal year calculated above.

From what you are saying above, the part explained at no 2 above is adding 1 to your year digits instead of deducting

You would rather need to use

="FY" + RIGHT(YEAR(Date@row) - IF(Date@row >= DATE(YEAR(Date@row), 6, 1), 1, 0), 2), wich will

• 1. YEAR(Date@row):
  • Extracts the year from the date in Date@row.

• 2. IF(Date@row >= DATE(YEAR(Date@row), 6, 1), 1, 0):
  • Checks if the date is on or after June 1st of the current year.
  • If Date@row is on or after June 1st, it subtracts 1 from the year.
  • If the date is before June 1st, no subtraction happens (returns 0).

• 3. YEAR(Date@row) - IF(..., 1, 0):
  • Adjusts the year based on the result of the IF statement. If the date is on or after June 1st, the fiscal year will be based on the previous calendar year (subtracts 1). If the date is before June 1st, the fiscal year remains the same as the current calendar year.

• 4. RIGHT(..., 2):
  • Extracts the last two digits of the adjusted year (e.g., for 2024, this will return 24).

• 5. "FY" + ...:
  • Concatenates the string "FY" with the two-digit fiscal year calculated above.

Hope this helps

Paul Newcome

Give this a try:

="FY" + RIGHT(YEAR(Date@row) - IF(Date@row < DATE(YEAR(Date@row), 6, 1), 1, 0), 2)