Date query - Look up? 1

[john434]

Hi

We are trying to write a function to work out if a date range (start Date - Now) pass over a set of specific dates. If the date range crosses a 'Spot Date' then we need to return the result '1', if the range passes 2 'Spot dates we need to return the result '2' and so on.

we have put the specific 'spot dates' into a table of their own to 'look up' to, but are scratching our heads furiously trying to find the correct function to complete this task.

Any help would be appreciated.

John n Nick

 

[xlbo]

If your SpotDates table is sorted ASCENDING, then you can use vlookup with the 4th argument set to TRUE to find out the last spot date that was passed:

=vlookup(startdate,ListOfDates,1,true)

=vlookup(today(),ListOfDates,1,true)

will give you the 1st and last spot dates in the range

Then, you can use logic, depending on how your table is ordered

=countif(ListOfDates,"<=" & vlookup(today(),ListOfDates,1,true)) - countif(ListOfDates,"<=" & vlookup(startdate,ListOfDates,1,true))


to make it a bit more readable it can be split into components. Lets say your list of spot dates is in A1:A100

Start date in B1

In C1:
=vlookup(B1,A1:A100,1,true)
In C2:
=vlookup(Today(),A1:A100,true)
In C3:
=countif(A1:A100,"<=" & C2)
in C4:
=countif(A1:A100,"<=" & C1)
in C5:
=C3-C4

hope this makes sense and I've understood the issue correctly

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[john434]

Cheers for your reply Geoff,

However, the Look up to find the first relevant Spot date =vlookup(startdate,listofdates,1,TRUE) is returning the nearest match as opposed to closest match (greater than the startdate). We have tried adding TRUE>D2, but this doesn't seem to solve the problem either.

Some of the date ranges we are dealing with do not cross any of the spot dates (these are the ones causing a problem) would it be better to nest the vlookup in a IF function to return a '0'?

Thanks for your help so far, I think we're getting there slowly, but would appreciate your continued help

John n Nick

 

[xlbo]

ok - sorry 'bout that - had it in my head that it was the other way round. Found where I got that idea from though - you need to replace the vlookup with an INDEX / MATCH

=INDEX(ListOfdates,MATCH(StartDate,ListOfDates,-1))

the ListOfDates range must be sorted DESCENDING for this to work...

the formula above should get the 1st spot date GREATER than the start date

Thinking about it, the coutif can be made easier as well:

=countif(ListOfDates,"<=" & Today()) - countif(ListOfDates, "<" & A1)

-where the INDEX MAtch formula is in A1 or:

=COUNTIF(ListOfDates,"<=" & TODAY())-COUNTIF(ListOfDates, "<" & INDEX(ListOfDates,MATCH(startDate,ListOfDates,-1)))

if you want it all in 1 cell

I named the spotdates range "ListOfDates" and sorted descending
I then named my start date cell "StartDate"

entered the formula in another cell and it seemed to work well


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[john434]

Geoff,

Thanks for all your help, we managed to get there in the end.

The countif function was a beauty, worked a treat.

Apologies for starting a new thread.

Have sent you a star for your help.

Cheers mate

John and Nick