compare dates using a variable
 

i need to know when 2 dates intersect
1-i begin with todays date and add a variable to it
2-i then subtract the same variable from a future date
3-i need to know at what date the 2 intersect

example: 7-16-2007 + 6=7-22-2007 5-10-2010 - 6=5-4-2010
then: 7-22-2007 + 6=7-28-2007 5-4-2010 - 6=4-28-2010, etc
this loop continues until they intersect.
////////////////////////////////////////////////thanks for your help so far, everyone!

John Doe's parole expires on 2/1/2008.
Each month on the 9th the parole date is reduced by 6 days.
If his parole expires before the 9th of the month, he will get 6 days on the 20th of the previous month.
When will John Doe's parole expire?
***the 6 represents a variable. It could be a 10, 12, 16 , 6****
The correct answer, calculated manually using the 6 is 12-27-2007.

compare dates using a variable
 

How do you define intersect?

Based on your sample they never intersect. The closest they come is:

From the start date: 12/13/2008
From the future date: 12/10/2008

--
Biff
Microsoft Excel MVP


"old coach" wrote in message
...

i need to know when 2 dates intersect
1-i begin with todays date and add a variable to it
2-i then subtract the same variable from a future date
3-i need to know at what date the 2 intersect

example: 7-16-2007 + 6=7-22-2007 5-10-2010 - 6=5-4-2010
then: 7-22-2007 + 6=7-28-2007 5-4-2010 - 6=4-28-2010,
etc
this loop continues until they intersect.




--
old coach

compare dates using a variable
 

i need to know when 2 dates intersect
1-i begin with todays date and add a variable to it
2-i then subtract the same variable from a future date
3-i need to know at what date the 2 intersect

example: 7-16-2007 + 6=7-22-2007 5-10-2010 - 6=5-4-2010
then: 7-22-2007 + 6=7-28-2007 5-4-2010 - 6=4-28-2010,
etc
this loop continues until they intersect.

Since you are alternately adding and subtracting the same amount, the
intersection date will be midway between them (think about it). So, if one
date is in A1 and the other is in B1, then the "intersection" date would
be..

=(A1+B1)/2

For your dates, that would be 12/11/2008... from a mathematical standpoint,
the size of the variable is immaterial as long as it is small enough
compared to the span between the dates. But you should note that this is an
idealized mathematical solution, so it is possible (probably likely) that
the number of iteration required to meet will not be a whole number. As
Biff's pointed out in his posting to this thread, the granularity of the
variable determines if they physically meet or not, so you will have to
figure out how you want to handle this situation. Notice my calculated date
is roughly midway between the dates Biff posted.

Rick

compare dates using a variable
 

Hi

If by intersect you mean that the resulting date is the same, then it
never will be with the example you posted.
With 10/May/2010 in A1 and 16/Jul/2007 in B1
=B1-A1 = 1029 days
=MOD(1029,6) =3
The dates can only be the same, in the case where MOD = 0.
i.e. there is no remainder when you divide the number of days difference
by 6.

The mid point will be
=(B1-A1)/2+A1
which is 11/Dec/2008 12:00

The nearest dates will be plus and minus 1.5 (half of the MOD value)
days from this date
="11/Dec/2008 12:00" -1.5 = 10/Dec/2008 00:00 and
="11/Dec/2008 12:00" +1.5 = 13/Dec/2008 00:00

--
Regards

Roger Govier


"old coach" wrote in message
...

i need to know when 2 dates intersect
1-i begin with todays date and add a variable to it
2-i then subtract the same variable from a future date
3-i need to know at what date the 2 intersect

example: 7-16-2007 + 6=7-22-2007 5-10-2010 - 6=5-4-2010
then: 7-22-2007 + 6=7-28-2007 5-4-2010 - 6=4-28-2010,
etc
this loop continues until they intersect.




--
old coach

compare dates using a variable
 

"Rick Rothstein (MVP - VB)" wrote in
message ...
i need to know when 2 dates intersect
1-i begin with todays date and add a variable to it
2-i then subtract the same variable from a future date
3-i need to know at what date the 2 intersect

example: 7-16-2007 + 6=7-22-2007 5-10-2010 - 6=5-4-2010
then: 7-22-2007 + 6=7-28-2007 5-4-2010 - 6=4-28-2010,
etc
this loop continues until they intersect.

Since you are alternately adding and subtracting the same amount, the
intersection date will be midway between them (think about it). So, if one
date is in A1 and the other is in B1, then the "intersection" date would
be..

=(A1+B1)/2

For your dates, that would be 12/11/2008... from a mathematical
standpoint, the size of the variable is immaterial as long as it is small
enough compared to the span between the dates. But you should note that
this is an idealized mathematical solution, so it is possible (probably
likely) that the number of iteration required to meet will not be a whole
number. As Biff's pointed out in his posting to this thread, the
granularity of the variable determines if they physically meet or not, so
you will have to figure out how you want to handle this situation. Notice
my calculated date is roughly midway between the dates Biff posted.

Rick

That's pretty much the conclusion I arrived at, too. So what's the purpose
of the "variable" ?

--
Biff
Microsoft Excel MVP