Results 1 to 5 of 5

Thread: Formula if one cell is within a range of numbers to return Year from next cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2007
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula if one cell is within a range of numbers to return Year from next cell

    I have four columns. A is calendar month, B is calendar year, C is Fiscal month, D is going to be Fiscal year. Our fiscal year is April to May, April = Fiscal month #1 , May = 2, etc.
    I need a formula for the fiscal year that will look at the fiscal month cell. Say C2 is either 1,2,3 fiscal month. My calendar year in B2 is 2018. I need it to return 2017 in D2 fiscal year.
    If C2 is 4 through 12, then I need it to return 2018 (same as the calendar year). Thanks for any suggestions or assistance.

  2. #2
    Board Regular
    Join Date
    Apr 2003
    Location
    England
    Posts
    5,890
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula if one cell is within a range of numbers to return Year from next cell

    Try

    =B1-(IF(MONTH(A1)<=3,1,0))

    Code:
    Jan 2018 Jan 2017
    Feb 2018 Feb 2017
    Mar 2018 Mar 2017
    Apr 2018 Apr 2018
    May 2018 May 2018
    Jun 2018 Jun 2018
    Jul 2018 Jul 2018
    Aug 2018 Aug 2018
    Sep 2018 Sep 2018
    Oct 2018 Oct 2018
    Nov 2018 Nov 2018
    Dec 2018 Dec 2018
    Jan 2019 Jan 2018
    Feb 2019 Feb 2018
    Mar 2019 Mar 2018
    Apr 2019 Apr 2019
    May 2019 May 2019
    Jun 2019 Jun 2019
    Jul 2019 Jul 2019
    Aug 2019 Aug 2019
    Sep 2019 Sep 2019
    Oct 2019 Oct 2019
    Nov 2019 Nov 2019
    Dec 2019 Dec 2019
    To post screen shots download the MrExcel HTML Maker
    Look here for other options Recommended Add-ins and Links

  3. #3
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,648
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Formula if one cell is within a range of numbers to return Year from next cell

    All sorts of ways to do this depending on what you actually have in the cells. If you mean the months are numbers then maybe:

    =B2-(C2>3)

  4. #4
    New Member
    Join Date
    Jan 2007
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula if one cell is within a range of numbers to return Year from next cell

    Tried and I'm getting an error. Stepping through it tells me the (MONTH(A1) part of the formula is #VALUE . Does the A column need to be formatted? I am testing it on a blank worksheet and typed Jan in A1 and C1. I have the formula in D1 but it is not working.


    Quote Originally Posted by gaz_chops View Post
    Try

    =B1-(IF(MONTH(A1)<=3,1,0))

    Code:
    Jan 2018 Jan 2017
    Feb 2018 Feb 2017
    Mar 2018 Mar 2017
    Apr 2018 Apr 2018
    May 2018 May 2018
    Jun 2018 Jun 2018
    Jul 2018 Jul 2018
    Aug 2018 Aug 2018
    Sep 2018 Sep 2018
    Oct 2018 Oct 2018
    Nov 2018 Nov 2018
    Dec 2018 Dec 2018
    Jan 2019 Jan 2018
    Feb 2019 Feb 2018
    Mar 2019 Mar 2018
    Apr 2019 Apr 2019
    May 2019 May 2019
    Jun 2019 Jun 2019
    Jul 2019 Jul 2019
    Aug 2019 Aug 2019
    Sep 2019 Sep 2019
    Oct 2019 Oct 2019
    Nov 2019 Nov 2019
    Dec 2019 Dec 2019

  5. #5
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,648
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Formula if one cell is within a range of numbers to return Year from next cell

    Use MONTH(1&A1) or if it still errors try MONTH(A1&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
  •