Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 24th 18, 06:51 AM
Junior Member
 
First recorded activity by ExcelBanter: Jul 2018
Posts: 2
Default 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   Report Post  
Old July 26th 18, 01:27 AM
Junior Member
 
First recorded activity by ExcelBanter: Jul 2018
Posts: 2
Default 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   Report Post  
Old July 30th 18, 01:10 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2009
Posts: 146
Default 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
To assign month for a certain range of date obc1126 Excel Programming 3 November 11th 08 08:48 AM
Date range - recognise end of each month Roger on Excel Excel Programming 2 August 26th 08 02:26 AM
1st Monday of a month in date range?? scwilly Excel Worksheet Functions 22 April 26th 06 04:49 AM
How do I break a date range by month? HeatherDawn Excel Discussion (Misc queries) 2 September 1st 05 07:31 PM
How do you sort a date range by month? Brewisc13 Excel Discussion (Misc queries) 13 July 7th 05 06:45 PM


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

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017