If a date is between 2 dates annually

wheellp

New Member
Joined
Aug 2, 2016
Messages
30
I am looking for a formula to determine if a date in column D is between the first day of October through last day of May from 2016-2021. I am looking to do this over several different years (Oct 2016-May 2017, Oct 2017- May 2018, Oct 2018- May 2019, etc.). I have a master spreadsheet with data from every year since 2016 that I am trying to add this to for reporting. Help please?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Post some sample data, easier to copy & paste your data than to try and guess what it looks like. Also, what your expected results should look like.
 
Upvote 0
Post some sample data, easier to copy & paste your data than to try and guess what it looks like. Also, what your expected results should look like.

Here is what the information looks like (had to hide several columns). I am trying to determine if the date in column D request date is between May of the previous year and October of year it was requested (goes back all the way to 2016). I am waiting the result to be a yes or no. Let me know if a screenshot won't work.
1592422113853.png
 
Upvote 0
Try

Create a small table like below
1592424041087.png


My table is in G4:H9 so change the references below to suit your data

Formula =

=IF(SUMPRODUCT((D1>=$G$4:$G$9)*(D1<=$H$4:$H$9)*1),"Yes","No")

1592424338560.png
 
Upvote 0
Try

Create a small table like below
View attachment 16424

My table is in G4:H9 so change the references below to suit your data

Formula =

=IF(SUMPRODUCT((D1>=$G$4:$G$9)*(D1<=$H$4:$H$9)*1),"Yes","No")

View attachment 16425
that is a great start but unfortunately not quite what I am looking for. For the request date I am looking for it to say yes if it falls between only one of the below date ranges (Oct-May). So if it was requested in the months June through September of that year it was requested it would say no.
1592425682089.png
 
Upvote 0
You need to be more detailed in what "you" are looking for, we can only give you a solution based on the info you give us, we have never seen your data before!
I can't see in your OP that you want to exclude the year of the Request date, is that what you are saying?
 
Upvote 0
You need to be more detailed in what "you" are looking for, we can only give you a solution based on the info you give us, we have never seen your data before!
I can't see in your OP that you want to exclude the year of the Request date, is that what you are saying?

Agreed, I should have added that in the OP. What about, if if the request date in column D falls in the months Oct, Nov, Dec, Jan, Feb, Mar, Apr, or May the result would be "yes" in column O (bid season). If the date falls within the months of Jun, Jul, Aug, or September the result would be "no" in column O (bid season). So to your comment, yes, not excluding the year.

1592427148952.png
 
Upvote 0
Try using the XL2BB option to post a sample of your data, posting a picture means we have to re-create your data manually.

Include in your sample the expected results.
 
Upvote 0
Simpler version but does the same without a table

=IF(OR(MONTH(D1)={1,2,3,4,5,10,11,12}),"Yes","No")
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top