Caculating dates inside days.

Cassim

New Member
Joined
Apr 4, 2014
Messages
4
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.
 

Some videos you may like

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
Joined
Apr 4, 2014
Messages
4
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
Joined
Jan 8, 2009
Messages
791
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

New Member
Joined
Apr 4, 2014
Messages
4
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
Joined
Apr 4, 2014
Messages
4
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

Well-known Member
Joined
Jan 8, 2009
Messages
791
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,367
Messages
5,444,050
Members
405,264
Latest member
JohnP1972

This Week's Hot Topics

Top