counting dates query

[john434]

We are workimg on a Excel speadsheet, trying to work out how many dates someone has gone over.

We have got our date range containing the 'SPOT dates' and have named it SPOT. Our mission in life at the moment is to write a formula to count the number of dates that have been covered by each record.

We have worked out the first and last dates that have been passed by using:

=IF(F8=0,IF(INDEX(SPOT,MATCH(D8,SPOT,-1))>D8,IF(INDEX(SPOT,MATCH(D8,SPOT,-1))<TODAY(),INDEX(SPOT,MATCH(D8,SPOT,-1)),0),0),IF(INDEX(SPOT,MATCH(D8,SPOT,-1))<=F8,INDEX(SPOT,MATCH(D8,SPOT,-1)),0))

and

=IF(F8=0,IF(INDEX(SPOT1,MATCH(TODAY(),SPOT1,1))>D8,IF(INDEX(SPOT1,MATCH(TODAY(),SPOT1,1))<TODAY(),INDEX(SPOT1,MATCH(TODAY(),SPOT1,1)),0),0),IF(INDEX(SPOT1,MATCH(F8,SPOT1,1))<TODAY(),IF(INDEX(SPOT1,MATCH(F8,SPOT1,1))>=D8,INDEX(SPOT1,MATCH(F8,SPOT1,1)),0)))

respectively. Now we just need to work out how many of the spot dates (SPOT) have been passed by each row. The formula needs to cover the first and last spot dates plus any others between that are included in "SPOT". The SPOT dates are three specific dates in a year and unfortunatly are not equally spaced throughout the year, so I don't think that we can use datedif or or days360.

Any Ideas greatfully recieved

John and Nick

 

[xlbo]

Did you try my 2nd responce in this thread:

thread68-889041 ??

Please do not start new threads for the same question - it is easier if people can see all that has gone before !

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