return the nth occurence

[AFST]

Hey all

I have a variety of dates in cells A1:A100. I want a formula in cell B1 that checks the A column for a date that is within 30 days of today (TODAY()-30) and returns the first occurrence that meets the criteria. Similarly in cell B2 i want it to do the same evaluation except return the second occurrence of a date within 30 days. Ultimately I'll do this 5 times, and if its all done correctly as you move down column B it should go from the oldest date within 30 days to the most recent. Can anyone lend a hand?

 

[ChrisOswald]

Regarding the merged cells: I'm guessing that you have merged cells like B1:B3, C1:C3, etc? if that's the case, what you can do is unmerged the cells, enter the formulas, and then re-merge them (at least in 2007).

Another alternative might be to lock the merged cells formula ranges and then format the cells so it doesn't look like they have any borders.

The ########## result is coming from when you've ran outside of the date range of returnable values (the 999999999 value that's generated for dates older than 30 days). You can fix it with this formula:

=IF(SMALL(IF(TODAY()-$A$35:$A$134<=30,$A$35:$A$134,99999999),COLUMN()-1)=99999999,"",SMALL(IF(TODAY()-$A$35:$A$134<=30,$A$35:$A$134,99999999),COLUMN()-1))

 

[AFST]

Great that solved the merge issue... at least I hope. I'm running 2007 but in compatibility mode as eventually it will be used by 2003 and hopefully it will still work.

I hate to say it but I've only just noticed this problem with the equation. It relies on the resultant cell being in the column immediate to the right of the dates, which is not my case. If everything was unmerged, the dates are still in column A, first occurrence should appear in column O, second occurrence in column U, third in column AA, fourth in column AG, and fifth in column AM. So yes when everything is merged all of the occurrence columns are next to each other but still offset from the date column. However, because I have to unmerge everything first its probably best to use the references I just listed. Hope that doesn't make a new formula too difficult.

So the way it is right now works, with first showing up in B and second in C etc. but I need the first in O second in U etc.

 

[ChrisOswald]

Replace Column()-1 in the formula with the 1 for the oldest, 2 for the second oldest, etc.