Excel 2007 : Finding dates

[shanuj]

Hi Guys,

I don't know if this is even possible but I thought I would ask anyway.

As per the attached sheet, I have a sheet with many dates which records when an activity is performed, whether the file was reported at the same time as the activity and the various dates that th efile was reported.

I need to be able to find where the activity happened at the same time as the file be reported and when the file stopped being reported after that. Using the below example, Policy 1 - the activity happened in Jan09 and was reported in the same month. The file stopped being reported in Mar 09, I need to record Mar09. Policy 2 - Activity happened in Feb 09 and reported in same month but not reported after, I need to record Feb 09.

policy # Activity reported reported reported reported reported reported
1 Jan 09 Dec08 Jan09 Feb09 Mar 09
policy # Activity reported reported reported reported reported reported
2 Feb 09 Dec08 Jan09 Feb09

So I guess in plain english the formula would be find date and look right until you find a blank cell and record the date immediately left of that.

Hope this makes sense......

[DonkeyOte]

The examples in the narrative don't tie out to your sample file so it's hard to know exactly what you're looking to do.

You can return the last numeric value in a range using:

=LOOKUP(9.99E+307,range)

Dates are numbers so the above should work for you (format result as date of course).

You might want to add a pre-emptive Count test should it be possible for a given ID to have no activity whatsoever.