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 that’s entered in the first cell is formatted as 2012-03-01.

 

Complicating things is the fact that the fiscal year is split in the middle; fiscal year 2022/2023 is from 2022-07-01 to 2023-06-30 and fiscal year 2023/2024 is from 2023-07-01 to 2024-06-30.

 

For example, if someone enters “06/30/2022” in the first cell, the next cell (in the column to the right) would populate with “2021/2022” but if they entered “07/01/2022” the next cell would populate with “2022/2023”. 

 

Any help would be greatly appreciated!

Thanks!

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))

6 Replies

  • 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.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    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 

         

        Just for fun, though, given that I've already marked Hans's reply as the best response, I thought this was a great situation to write a LAMBDA function. So in my revised sheet, attached, you can just enter a date in, say, cell A4, and then in any other cell, 

        =Fiscal(A4)

        to get the corresponding Fiscal Year.

        If LAMBDA functions are new to you, here's a reference that explains how to do them.

         

Resources