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

#### bamroll

##### New Member
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.

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Try

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

Code:
``````[TABLE="width: 348"]
<colgroup><col width="87" span="4" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 87, align: right"]Jan[/TD]
[TD="width: 87, align: right"]2018[/TD]
[TD="class: xl63, width: 87, align: right"]Jan[/TD]
[TD="width: 87, align: right"]2017[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Feb[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Feb[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Mar[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Mar[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Apr[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Apr[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]May[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]May[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Jun[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Jun[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Jul[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Jul[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Aug[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Aug[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Sep[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Sep[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Oct[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Oct[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Nov[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Nov[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Dec[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Dec[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Jan[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Jan[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Feb[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Feb[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Mar[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Mar[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Apr[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Apr[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]May[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]May[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Jun[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Jun[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Jul[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Jul[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Aug[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Aug[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Sep[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Sep[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Oct[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Oct[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Nov[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Nov[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Dec[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Dec[/TD]
[TD="align: right"]2019[/TD]
[/TR]
</tbody>[/TABLE]``````

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)

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.

Try

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

Code:
``````[TABLE="width: 348"]
<colgroup><col width="87" span="4" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 87, align: right"]Jan[/TD]
[TD="width: 87, align: right"]2018[/TD]
[TD="class: xl63, width: 87, align: right"]Jan[/TD]
[TD="width: 87, align: right"]2017[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Feb[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Feb[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Mar[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Mar[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Apr[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Apr[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]May[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]May[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Jun[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Jun[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Jul[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Jul[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Aug[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Aug[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Sep[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Sep[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Oct[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Oct[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Nov[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Nov[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Dec[/TD]
[TD="align: right"]2018[/TD]
[TD="class: xl63, align: right"]Dec[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Jan[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Jan[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Feb[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Feb[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Mar[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Mar[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Apr[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Apr[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]May[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]May[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Jun[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Jun[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Jul[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Jul[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Aug[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Aug[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Sep[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Sep[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Oct[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Oct[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Nov[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Nov[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Dec[/TD]
[TD="align: right"]2019[/TD]
[TD="class: xl63, align: right"]Dec[/TD]
[TD="align: right"]2019[/TD]
[/TR]
</tbody>[/TABLE]``````

Use MONTH(1&A1) or if it still errors try MONTH(A1&1)

Replies
1
Views
339
Replies
9
Views
290
Replies
3
Views
569
Replies
6
Views
423
Replies
5
Views
172

1,203,741
Messages
6,057,102
Members
444,905
Latest member
Iamtryingman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back