Remember Me?

#1
July 24th 18, 06:51 AM
 Junior Member First recorded activity by ExcelBanter: Jul 2018 Posts: 2
Search Month within date range

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!!!!

#2
July 26th 18, 01:27 AM
 Junior Member First recorded activity by ExcelBanter: Jul 2018 Posts: 2
Help?

Hey all. So is this something that someone can shed some light on. I've continued to Google the issue to no avail.
#3
July 30th 18, 01:10 AM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Oct 2009 Posts: 146
Search Month within date range

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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post obc1126 Excel Programming 3 November 11th 08 08:48 AM Roger on Excel Excel Programming 2 August 26th 08 02:26 AM scwilly Excel Worksheet Functions 22 April 26th 06 04:49 AM HeatherDawn Excel Discussion (Misc queries) 2 September 1st 05 07:31 PM Brewisc13 Excel Discussion (Misc queries) 13 July 7th 05 06:45 PM

All times are GMT +1. The time now is 02:25 PM.