Caculating dates inside days.

[Cassim]

Hi Team,
I am new here and I need your help to solve a tiny problem that I am having.

I am looking for a way to calculate dates inside days, so dates I have are below and days can be any number (lets call it 34 as an example).

20/01/14
07/03/14
26/03/14
18/04/14
24/05/14
21/06/14

So the 2nd date 07/03/14 minus 34 days is equal to 01/02/14 which is greater/equal to the 1st date 20/01/14 so I want the answer to be "Yes", the 3rd date 26/03/14 minus 34 is equal to 20/02/14 which is less than the previous date so the I want answer to be "No" (I am OK up to this point).

Here comes the tricky bit!
The forth date 18/04/14 minus 34 days is equal to 15/03/14, for which I`ll get "No" based on the above conditions but instead I want the answer to be "Yes" because I want the system to calculate it from the date which has "Yes" the last time. Instead of using of using 3rd date 26/03/14 it should go back to 2nd date 07/01/14 to get the answer.

20/01/14 - Blank
07/03/14 - Yes
26/03/14 - No
18/04/14 - Yes
24/05/14 - Yes
21/06/14 - No

Many thanks in advance.

 

[Cassim]

I forgot to mention that these dates are not in consecutive rows, there are other dates for different parts in the list as well!

 

[mfexcel]

Assume you data starts at A1

In B2
=IF((A2-A1)>=34,"yes","no")

In B3, copy down
=IF((A3-INDEX($A$1:A2,MAX(IF($B$2:B2="yes",ROW($B$2:B2)))))>=34,"Yes","No")
CTRL SHIFT ENTER

Hi Team,
I am new here and I need your help to solve a tiny problem that I am having.

I am looking for a way to calculate dates inside days, so dates I have are below and days can be any number (lets call it 34 as an example).

20/01/14
07/03/14
26/03/14
18/04/14
24/05/14
21/06/14

So the 2nd date 07/03/14 minus 34 days is equal to 01/02/14 which is greater/equal to the 1st date 20/01/14 so I want the answer to be "Yes", the 3rd date 26/03/14 minus 34 is equal to 20/02/14 which is less than the previous date so the I want answer to be "No" (I am OK up to this point).

Here comes the tricky bit!
The forth date 18/04/14 minus 34 days is equal to 15/03/14, for which I`ll get "No" based on the above conditions but instead I want the answer to be "Yes" because I want the system to calculate it from the date which has "Yes" the last time. Instead of using of using 3rd date 26/03/14 it should go back to 2nd date 07/01/14 to get the answer.

20/01/14 - Blank
07/03/14 - Yes
26/03/14 - No
18/04/14 - Yes
24/05/14 - Yes
21/06/14 - No

Many thanks in advance.

 

[Cassim]

Many thanks mfexcel half way there
Is there anything I can do to tie the calculation to a part reference ?
These dates are not in consecutive rows, there are other dates for different parts in the list as well!

Assume you data starts at A1

In B2
=IF((A2-A1)>=34,"yes","no")

In B3, copy down
=IF((A3-INDEX($A$1:A2,MAX(IF($B$2:B2="yes",ROW($B$2:B2)))))>=34,"Yes","No")
CTRL SHIFT ENTER

 

[Cassim]

Hi All,

I worked it out ...

=IF(COUNTIF($A$1:A7,A7)=1,"Yes",IF(MAX(IF($A$1:A6=A7,$B$1:B6))<=B7-34,"Yes",IF((B7-INDEX($B$1:B6,MAX(IF($C$1:C6="Yes",ROW($C$1:C6)))))>=34,"Yes","-")))

AA:Part Number
BB: Dates
CC: criteria/formula column

Ctrl+Alt+Enter

Thanks for your help.

 

[mfexcel]

Glad that you could figure out a solution on your owns. cheers.

Hi All,

I worked it out ...

=IF(COUNTIF($A$1:A7,A7)=1,"Yes",IF(MAX(IF($A$1:A6=A7,$B$1:B6))<=B7-34,"Yes",IF((B7-INDEX($B$1:B6,MAX(IF($C$1:C6="Yes",ROW($C$1:C6)))))>=34,"Yes","-")))

AA:Part Number
BB: Dates
CC: criteria/formula column

Ctrl+Alt+Enter

Thanks for your help.