[bosshard]

Hi all,

New to the forum so hope that someone can give me a bit of guidance. Thanks in advance.

I dont have a programming background so I was looking for the most simple solution to the following problem.

I want to determine whether a month (i.e. July) falls between two date ranges (1-7-2018 and 1-9-2018). Is this something that can be done??

Just for those more visually inclined:


D E M N O
4. July
488. 123,456
491. 1-7-2018 1-9-2018
500. 123,456

Q. Does July (N4) sit between these two dates(D491 and E491)? If yes, how do I have it spit out a value into cell o500, such that if the date does fall between the ranges, then it provides the value of cell M4 in cell o500?

I've tried both of these to no avail. Am I on the right track?

- - Working within cell o500) - -

=IF(AND($N$4=D491, $N$4<=E491),"na", M491)

=if(and(month(N$4)=date($D488),month(N$4)<=date($ E488)),$M488,"na")

Any help would be appreciated.

Thank you!!!!

[bosshard]

Hey all. So is this something that someone can shed some light on. I've continued to Google the issue to no avail.

[zvkmpw]

Hey all. So is this something that someone can shed some light on.

Let's start with a simpler problem statement: "when N4, D491, and E491 all contain date values, does N4 fall between the other two (inclusive)?" For this, consider:
=IF(MEDIAN($N$4,D491,E491)=$N$4, M491, "na")

If that's not the intention, does N4 actually contain a date value, or a text string?

If N4 contains a string and if the dates to be compared are all the first of the month, then consider:
=IF(MEDIAN(DATEVALUE($N$4&" 1, 2018"),D491,E491)=DATEVALUE($N$4&" 1, 2018"),
M491,"na")

I'm not sure if this covers the problem intended in th original post. Other possibilities might be :
- The first of N4's month is compared with D491 and E491
- The entire month of N4 must be between the two dates D491 and E491
- something else.

Hopefully, the formulas above can be a starting point in these cases. Other functions than might be useful include EOMONTH(...) and DATE(...).

If more is needed, please be a lot more specific about the problem statement, what you tried, and how the result you got differed from what's needed.