if values between x and y date each year output yes

Hi,

I have data as far back as 2000 and I want to output a value of yes if the date is between two dates for each year. For example if the date is between the 15th of September 2000/1/2/3/4 etc and the 15th of April 2001/2/3/4/5 etc, output yes, otherwise output no. It would be for each 15th of September to 15th of April every year.

Is there an easy method of doing this?

I haven't been having much luck in figuring this one out, your help would be very much welcomed.

Regards,
Dewey

Originally Posted by jeffreybrown

=IF(AND(A1>DATE(2000,9,15),A1<DATE(2001,4,15)),"Yes","Nope")

Thank you for that but that only works for one time period, I would like it to be done for each year, maybe if I put in an or statement for each year but that would make the equation very long.

Dewey

Originally Posted by AliGW

Are you still using Excel 2013?

Nope using 2019 now, just updated it, I forgot to update it.

So the attached document is what I have, its a selection of dates between 2000 and 2023.
I want the values between 15th September 200(n) and 15th April 200(n+1) to output a value. In this case Yes or Nope.

I have used the formula provided above and inputted it into a long if, or statement.
The issue with this way is that the formula is long already and I want to add in a formula where if yes comes back calculate X and if no comes back then output 0.


Is there a way to condense down the or statement?

Regards,
Stephen

Originally Posted by protonLeah

Try:

Please Login or Register  to view this content.

Hi Ben Van Johnson,

Thank you for that but that returns results that shows out of range when it should show within range. for example it shows the 14th of February as being out of range but it should show it within range

Originally Posted by MrShorty

Did you try Jeff's suggestion? I was thinking the same kind of strategy -- choose a year (I would choose a leap year) and convert each date to that the same date for that year, then decide if it is in the desired date range or not.


2) If I understand the logic, if this date is between April 15 and Sept 15, then output "Nope", else output "Yes." =IF(AND(DATE(1900,4,15)<=E2,E2<=DATE(1900,9,15)),"Nope","Yes") or =IF(AND(DATE(1900,4,15)<=E2,E2<=DATE(1900,9,15)),calculation if Nope,calculation if Yes)

Did I understand correctly, or did I misunderstand something?

Hi Mr Shorty,

I did try Jeffs suggestion and it worked, the only "problem" is for it to work for each year, a large equation is required for each year.

You understood it perfectly.

Originally Posted by alansidman

The attached file contains a parameter query in Power Query. You select the start and end dates in the fields shown. click on Refresh All and your data is updated.

Thank you for that,

I do not have power query in excel and unsure what it is.

Originally Posted by bebo021999

For any given year, the number of days from September 15 of that year to January 1 of the following year is always 108 days.
Therefore, the time period from September 15 of a given year to April 15 of the following year, when adjusted to the same year, also add (+) 108 days. This is true regardless of whether it's a leap year or a regular year.

So, for any given date, to determine whether it falls within this time frame, you can add (+) 108 days to that date and then compare it with January 1 and August 1.

If A2 is the original date, then A2 + 108 is the date for calculation. You can compare this date with January 1 and August 1 following year to determine if it falls within the specified time frame. If the date is between January 1 and August 1, the result is "Yes," otherwise, it's "Nope."

And, the final formula should be:

Please Login or Register  to view this content.

P/S: compare with your manual result in row 43, with A43 = 10-Oct-2010, it should be "Yes" as per my formula, but your desired outcome is "Nope"
Is it correct?

Thank you for this. This is what I was looking for.

You are correct with respect to the 10th of October, it should have been a Yes.


I have been looking at the equation, trying to understand it more but finding it difficult to figure out when the equation "knows" it is the 15th of April.

The first part of the equation checks the data is from 2001 and above, anything below that is outputted as "Nope",

The OR statement indicates that if the month plus the number of days from the 15th of September to the 1st of January is less than 8, a true statement is outputted, otherwise a false statement is outputted.
The AND statement indicates that if the month plus the number of days from the 15th of September to the 1st of January is equal to 8 and the date plus number of days from the 15th of September to the 1st of January is equal to 1, a true statement is outputted, otherwise a false statement is outputted.

The second IF statement indicates that if the OR statement and/or the AND statement outputs a true statement, then "Yes" is outputted, but if neither statements output a true statement, then "Nope" is outputted.


If I want to change the start date from the 15th of September to the 10th or the 27th, the 108 days would be updated accordingly, but that equation automatically changes the end dates of the 15th of April also by the difference in the new start dates and 108 days.

Any insight into how the equation "knows" when the 15th of April is reached would be greatly welcomed.

Dewey

Originally Posted by AliGW

Administrative Note

Please don't ignore contributors to your thread - it takes members time to provide suggestions, so acknowledge all solutions offered, even if they don't meet your requirements. Thanks.

My apologies if it looked like I was ignoring contributors, I was working through the potential solutions as I saw them before replying back, to know if a solution was working or not and sometimes get distracted by other things.

Hi,

Thank you for the reply and explanation.

Unfortunately it didn't at the start but I worked out what you were saying.


There appears to be some mix ups in the Case examples and in Case 3, shouldn't it be <7 and not 6?

It is also based on the 15th of April and not the 1st of April.

I think I have adjusted it so that it flows and conveys what your explanation is.



Ultimately I am trying to also be able to change when the dates start and end, so it might be 17th of September until the 3rd of April.


I have spent most of the day trying to play around with the formula to make it work, but I am getting no where fast.

I would post the formula I figured out to date but excel crashed and I didn't save it :/ but will try to recreate it.

The start dated was always the new date I wanted. For example if I changed the 108 to cell k4 (which counted the difference in the dates between the new start date e.g. 27th September and 1st January) but if I kept the end date the 15th April, it would always increase it to the dated in April that is the difference between the 27th of September and 1st January e.g. instead of the 15th April, it would be 27th April.

I am trying to edit the formula to allow it reference a cell which mentions the start date and the end date but I'm not having much luck.

Is it possible with your equation


Regards,
Dewey

Hi all,

Thanks for your help but in the end I was able to work it out.

The attached document is my solution.

If you input the end date and start date with a year, it will check that the date being reference is less than the day, month and year of the starting date or that the date being referenced is greater than the day, month and year of the end date.

I have checked it and it appears to work for any start and end date inputted.

As the data inputted will only be for the years that is relevant, there is no issue with the year being effectively irrelevant in the calculation.

Thank you all again for your help.

Kind Regards,
Dewey