Help with formula looking at Date ranges?

Hi,
what is the relationship between start date, maintenance exp date and the renwal interval?

Hi Arthur, renewal internval is every is 12months, relationship between start date and maintenance exp date is the date the software was purchased until it then expires.

would something like this potentially work as a starting point to determine if the startdate fell into the range?

=if(and(a1>=e1,a1<=e2,a1,"")

assuming e1 was 01/04/2007 and e2 was 31/03/2008

then the same thing could be repeated on the maintenance expiry column.

If that would work, I'd just need to then do something to compare the two?

Try
=if(and(a1<e1,b1>e2),"yes",if((a1<e1,b1>=e1,b1<=e2),"no","new sale"))

( not tested, don't have XL on this PC - sorry)

Originally Posted by arthurbr

Try
=if(and(a1<e1,b1>e2),"yes",if((a1<e1,b1>=e1,b1<=e2),"no","new sale"))

( not tested, don't have XL on this PC - sorry)

just tried this an unfortuntely it returns #VALUE!

any ideas/suggestions greatly appreciated as I am utterly stuck

Originally Posted by shg

Missing a function, maybe AND: =IF(AND(A1<E1, B1>E2), "yes", IF(AND(A1<E1, B1>=E1, B1<=E2), "no", "new sale") )

Shg's formula works perfectly for me

thanks for the help guys I got there in the end....here is what I used.

=IF(AND($H2>=V$1,$H2<W$1),"New Sale",IF(AND($H2<V$1,$I2>=W$1),"Yes",IF(AND($H2<V$1,$I2<W$1),"No",IF(OR($H2>=W$1,$I2<V$1),"-","?"))))

Originally Posted by oldchippy

Hi,

Does this help?

thanks BUT that isn't what I want perhaps I wasn't clear, my head is at a bit tangled by it all.....

I've attached a new sheet to show you the results I would expect to getting....columns a/b are the start/maintenance date range and columns c/d are the results I'd expect to get from the formula for the date ranges:

01/04/06 - 31/03/07
01/04/07 - 31/03/08

thanks for the help so far.....

Hi - I have a similar issue but I don't appear to be able to post a new thread so I'm using this if that's OK.

Basically, I want to see if a date in field B2 (e.g. 01/01/09) appears in a range of dates (e.g. C2:D30) and if it does, to return a 1, else return nothing.

I can get this to work for specified cells but I can't get it to work for a range. The code I've used is pasted below - I can keep extending to check each row but it's going to end quite long as there are going to be up to 30 rows to check. Any ideas?

=IF(AND(H2>=$B$2,H2<=$C$2,H2>=$B$3,H2<=$C$3,H2>=$B$4,H2<$C$4),1,0)

Thank you