Results 1 to 3 of 3

Thread: Semi-Dynamic Fiscal Year & Quarter Formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,058
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Semi-Dynamic Fiscal Year & Quarter Formula

    I have a workbook with the following dynamic formula that determines the fiscal year and quarter based on todays date. When I run the formula today it will result in "FY 2019 Q2" but when I open the same workbook in April 2019 it will change to "FY 2019 Q3" but I do not want that to happen when the window for that fiscal year and quarter changes, essentially making the formula from fully dynamic to semi-dynamic. How can I achieve this?

    ="FY "&YEAR(TODAY())-(MONTH(TODAY())<10)&" "&LOOKUP(MONTH(TODAY()),{1,"Q2";4,"Q3";7,"Q4";10,"Q1"})

    *Note* I do not want to paste the formula as values. I like to keep it semi-automated.
    Last edited by legalhustler; Jan 17th, 2019 at 10:58 AM.

  2. #2
    Board Regular
    Join Date
    Oct 2016
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Semi-Dynamic Fiscal Year & Quarter Formula

    Instead of using TODAY in the formula, refer to another cell where you put a date. Then, unless you change the date, the result will hold.

    Regards,
    Rui

  3. #3
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,613
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Semi-Dynamic Fiscal Year & Quarter Formula

    That formula returns FY 2018 Q2 today.

    With the date of interest in A2,

    ="FY " & YEAR(A2) + (MONTH(A2) > 9) & " Q" & LOOKUP(MONTH(A2), {1,4,7,10}, {2,3,4,1})

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •