Max date not after certain date

[miamited]

I have a spreadsheet that has 6 columns of dates, G thru L, that I need the max date that it is not later the date in column D. I need the result in column N


Sample data:

D F G H I J K L

7/30/2012 12/27/2012 blank 12/27/2012 12/27/2012 blank 12/27/2012 12/27/2012

8/7/2012 12/28/2012 blank 6/21/2012 8/3/2012 6/13/2012 12/28/2012 8/3/2012

7/10/2012 11/29/2012 7/9/2012 7/3/2012 7/9/2012 7/9/2012 11/29/2012 7/9/2012


I can get the max date from G thru L, but can not figure out how to make sure that it is before D


Thanks.

 

[jason]

do you want the max date for each row or for the whole range of dates?


if one for each row, i would use the if funciton:

=IF(MAX(E1:K1)>D1,"",MAX(E1:K1))


Scratch that... i think you'll want <D1

>D1 if you want the date to be before the date in D1

<D1 if you want the date to be after the date in D1

 

[miamited]

For each row.


That works, but if any of the dates in columns G thru L are later than the date in Column D, it returns a blank cell. I need it to return the max date prior to the date in Column D.

 

[jason]

and if you change the <D1 or >D1 part in my formula, neither gives you the outcome you are wanting?


i guess im not following 100%. could you take the dates you posted originally and give the result you wish to be returned?

 

[miamited]

Maybe I am not explaining it properly,


I put what you suggested in the cell, only change was to reference the correct cells.

=IF(MAX(G2:L2)>D2,"",MAX(G2:L2))


If the max from G2:L2 is greater than the date in D2, then the cell is blank.

If the max from G2:L2 is less than the date in D2, then the cell shows the max date.


I tried changing it to =IF(MAX(G2:L2)>D2,MAX(G2:L2)<D2,MAX(G2:L2))

and get false if the max from the range is greater. If the max in the range is before the date in D2, then it returns the correct max date.


The dates in G thru L are dates different documents (loan contract, police report, etc) are received. It is possible to receive multiple copies of the same document on different dates.


The date is D is the date the claim is processed.


I need to calculate the number of days between the date the last document was received and the claim was processed. I need to eliminate the dates documents received after the claim was processed (multiple copies of the same documents) otherwise it looks like the claim was processed prior to the documents being received and I end up with a negative date count.


In the case of the second line, multiple copies of documents were received both prior to, and after the claim was processed. Claim processed on 8/7/12, document received dates are 12/28/12 which is after date processed, the other documents were received on 6/21/12, 8/3/12, 6/13/12, and 8/3/12. I would like the formula in this row to return 8/3/12 since that is the max date prior to the claim processed date of 8/7/12.


Hopefully this explains what I am trying to do better.

 

[jason]

7/30/2012 | 12/27/2012 | blank | 12/27/2012 | 12/27/2012 | blank | 12/27/2012 | 12/27/2012

This row has the same dates for the docs recieved... is this accurate/possible? if so this will, as you said, return a negative amount of days for the life of the claim. (date claim was processed - date of last recieved file)


8/7/2012 1| 2/28/2012 | blank | 6/21/2012 | 8/3/2012 | 6/13/2012 | 12/28/2012 | 8/3/2012

In this row, we want 8/3/12 returned? ultimately showing that 4 days passed from the last day of file received to the date when the claim was processed?


7/10/2012 | 11/29/2012 | 7/9/2012 | 7/3/2012 | 7/9/2012 | 7/9/2012 | 11/29/2012 | 7/9/2012

Here we would want 7/9/12? returned??


you only want to look at the last date for files recieved BEFORE the date the claim was processed? sorry for all the confusion. it may be helpful to upload a sample spreadsheet if you can...

 

[miamited]

1) It is possible for all the docs to be received on the same day. There are 7 different docs.


2) That is correct.


3) That is correct.


4) Yes, I want the max date prior to the date processed (first column)

 

[jason]

AH HA!!! now I'm picking up what you're putting down my friend!!


ok, one more quesiton: what if all the dates received are AFTER the date processed? (first line of dates)???


I'll get back to you

 

[miamited]

That would not be possible as the claim could not have been processed without documents being received.


This did bring up a very good point. The query that I used to mine the data just pulled the last date each document was received, not all the dates that each doc was recevied. I re-wrote the query limiting the date each doc was received to be <= the date the claim was processed, for each doc. That has eliminated any dates listed for docs recevied date after the date processed date. That basicly did what I needed to do. It was an issue with the data, not the processing in Excel.


I guess that you can consider this one closed. If you want to continue to play with it, The result may help someone else.


Thank you for all your time.

 

[jason]

ok, just wondering since the first line of dates that you gave was all 12/27/12 but the first date (date claim processed was 7/30/12.


not getting too far since the dates will nto be in sequential order.....still looking/working

 

[SirJB7]

Hi, miamited!


Try this formula in N2 and copy down as needed:

=SUMAPRODUCTO(MAX(($G2:$L2<$D2)*($G2:$L2>0)*($G2:$L2))) -----> in english: =SUMPRODUCT(MAX(($G2:$L2<$D2)*($G2:$L2>0)*($G2:$L2)))


Just advise if any issue.


Regards!