Finding nearest date(others1-5) compared to Column C's dates(birthday).
Criteria
--------
1. must not be after column C's dates
2. must be at least 4 days apart
attached comments in spreadsheet.
Date Example.xlsx
Thanks in advance!
Finding nearest date(others1-5) compared to Column C's dates(birthday).
Criteria
--------
1. must not be after column C's dates
2. must be at least 4 days apart
attached comments in spreadsheet.
Date Example.xlsx
Thanks in advance!
Maybe one possibility...
=IF(C3-INDEX(D3:H3,MATCH(C3,D3:H3))>4,INDEX(D3:H3,MATCH(C3,D3:H3)),INDEX(D3:H3,MATCH(C3,D3:H3)-1))
HTH
Regards, Jeff
Hi Jeffreybrown, thanks for the reply.
What if the others1-5 dates are in random order? which would affect the first part of the formula in some circumstances i think
Last edited by Dwexdwex; 08-18-2013 at 01:44 AM.
Yes it would affect the outcome.
If your workbook is setup different than the one you posted, please post your real requirements.
Last edited by jeffreybrown; 08-18-2013 at 09:15 AM.
As far as I understand your problem, the expected result of row 5 is not correct. The result should be July 1 and not July 6.
This compares pairs of columns and if they are 4 days apart and not past the birthday then this appears to work...at least as far as I was able to test.
Formula:Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks