• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Paydates date range

C

camrose

New Member
Hi there,

I have a list of PayDates and I want to return a value within that range if a date in the PayDates range falls within two dates (in different cells)


So I want to return the pay date that falls between two dates (A1) and (B1)


For example: The first value in the PayDates range is 10/2/12

If the values in A1 and B1 are 10/1/12 and 10/8/12, I want the cell in C1 to return 10/2/12 (the value in the pay dates range).


Does this make sense?


Please let me know if I left out any pertinent information in assisting me and I thank you in advance!


Cameron
 
I tried this in the cells and it only returns 0.

I am not versed in SUMPRODUCT formula so I do not know how to troubleshoot this myself.


Can I attach a sample sheet for you to view?
 
Hmm. Okay, let's try going the other way:

Assuming PayDates are in A2:A12

=SUMPRODUCT(MAX((A2:A12>=A1)*(A2:A12<=B1)*(A2:A12)))
 
Hi, camrose!

If considering the upload of a sample file give a look at the second green sticky post at this forums main page for uploading guidelines.

Regards!
 
Hi ,


Try this :


=INDEX(PayDates,MATCH(1,((PayDates>=A1)*(PayDates<=B1)),0))


entered as an array formula , using CTRL SHIFT ENTER ; this will return the first value in the list , which matches the criteria dates given in A1 and B1. PayDates is your list of dates.


Narayan
 
HOORAY!

I got this to work: =SUMPRODUCT(MAX((A2:A12>=A1)*(A2:A12<=B1)*(A2:A12)))


thanks to you all!
 
LUKE, You are the such a master of Sumproduct....i never seen anybody using sumproduct in as much ways as you use it. You have really exploit this function.


Regards,
 
thanks Kuldeep! It really is prety powerful, since it lets you handle arrays very well and do multiple math operations. Limitation is that if you get too many, you'll start seeing significant slow down due to calculation time.


Again, thanks for the kind words.
 
You must log in or register to reply here.
Back
Top