# Caculating dates inside days.

#### Cassim

##### New Member
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

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Cassim

##### New Member
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

##### Well-known Member
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

#### Cassim

##### New Member
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

##### New Member
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

#### mfexcel

##### Well-known Member
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