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

1. ## 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. ## 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

```

3. ## 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. ## 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.

Originally Posted by gaz_chops
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. ## 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)