Forum Discussion

Rome707's avatar
Rome707
Copper Contributor
May 03, 2023
Solved

Auto Populating Fiscal Year in a Cell Based on a Date Entered in Another Cell

Hi everyone. I’m working on a database and am trying to figure out how to make a cell auto populate with a fiscal year (ie 2022/2023) based on a date that’s entered in the preceding cell. The date th...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    May 03, 2023

    Rome707 

    Let's say you enter a date in A1.

    The fiscal year is returned by the formula

    =YEAR(EDATE(A1,-6))&"/"&YEAR(EDATE(A1,6))

mathetes's avatar
mathetes
Gold Contributor
May 03, 2023

Rome707 

 

If you have Excel 2021 or newer (Microsoft 365 subscription), then this formula will work:

=LET(refyr,YEAR(A2),Fiscal1,IF(A2<=DATE(refyr,6,30),refyr-1,refyr),TEXT(Fiscal1,"0000")&"/"&TEXT(Fiscal1+1,"0000"))

 

 See the attached  spreadsheet for the working example.

Resources