If date is in certain month return year range (ex. 2018-2019)

wheellp

New Member
Joined
Aug 2, 2016
Messages
27
I need a hero. I have a spreadsheet spread over a few years and am looking for a formula to help with the following. We have what is called a "bid season" that is from October through May. I am trying to have a value returned in column F to combine years if a date in column C is between Oct-May and if not between those months (June-Sept) to be blank . Example if the date =10/6/2018 or 4/9/2019 that the value returned would be 2018-2019 and so on for 1,100+ rows spanning from 2017 to present. See example data below. Let me know if I need to clarify anything please.

Book3
ABCDEFG
1CUSTOMERREQUESTED BYREQUEST DATEBid Season (Oct to May)MonthBid Season yearExample without formula
2Customer 1Employee 11/3/2017Yes12016-2017
3Customer 2Employee 22/10/2017Yes22016-2017
4Customer 3Employee 37/13/2017No2(blank- not in Oct-May)
5Customer 4Employee 411/24/2017Yes22017-2018
6Customer 5Employee 51/3/2018Yes22017-2018
7Customer 6Employee 62/10/2018Yes22017-2018
8Customer 7Employee 77/13/2018No2(blank- not in Oct-May)
9Customer 8Employee 811/24/2018Yes22018-2019
10Customer 9Employee 91/3/2019Yes22018-2019
11Customer 10Employee 102/10/2019Yes22018-2019
12Customer 11Employee 117/13/2019No2(blank- not in Oct-May)
13Customer 12Employee 1211/24/2019Yes22019-2020
14Customer 13Employee 131/3/2020Yes22019-2020
15Customer 14Employee 142/10/2020Yes22019-2020
16Customer 15Employee 157/13/2020No2(blank- not in Oct-May)
17Customer 16Employee 1611/24/2020Yes22020-2021
Sheet1
Cell Formulas
RangeFormula
D2:D17D2=IF(OR(MONTH(C2)={1,2,3,4,5,10,11,12}),"Yes","No")
E2:E17E2=MONTH('Profile Request Tracker Report AUTO.xlsm'!All[@[REQUEST DATE]])
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,595
See if the following works for you:

=IF(MONTH(EDATE(C2,-9))<9,YEAR(EDATE(C2,-9))&"-"&YEAR(EDATE(C2,3)),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,112,863
Messages
5,542,941
Members
410,577
Latest member
ZvK
Top