Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. Last edited by old coach : July 17th 07 at 11:52 PM Reason: actual situation |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare dates | Excel Worksheet Functions | |||
Compare two dates and if they are equal | Excel Discussion (Misc queries) | |||
how do I compare two dates? | Excel Worksheet Functions | |||
Variable dates on X axis evenly spaced (should not be) | Charts and Charting in Excel | |||
Compare range of dates and sum... | Excel Discussion (Misc queries) |