VBA or IF problem

[jhonatanbenami]

Here is my data:

[TABLE="width: 1295"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]A[/TD]
[TD="align: left"]B[/TD]
[TD="align: left"]C[/TD]
[TD="align: left"]D[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]F[/TD]
[TD="align: left"]G[/TD]
[/TR]
[TR]
[TD]application number[/TD]
[TD]application number start date[/TD]
[TD]task name[/TD]
[TD]ID[/TD]
[TD]task start date[/TD]
[TD]task end date[/TD]
[TD]application number End date[/TD]
[/TR]
[TR]
[TD]6256372[/TD]
[TD]16/07/2014[/TD]
[TD]missing/false documents[/TD]
[TD]63298632[/TD]
[TD]19/01/2016[/TD]
[TD]19/01/2016[/TD]
[TD]26/01/2016[/TD]
[/TR]
[TR]
[TD]6256372[/TD]
[TD]16/07/2014[/TD]
[TD]cash request[/TD]
[TD]63298632[/TD]
[TD]03/01/2016[/TD]
[TD]20/01/2016[/TD]
[TD]26/01/2016[/TD]
[/TR]
[TR]
[TD]6715072[/TD]
[TD]08/01/2015[/TD]
[TD]missing/false documents[/TD]
[TD]314019381[/TD]
[TD]28/10/2015[/TD]
[TD]21/01/2016[/TD]
[TD]25/01/2016[/TD]
[/TR]
[TR]
[TD]6867668[/TD]
[TD]19/02/2015[/TD]
[TD]missing/false documents[/TD]
[TD]16534356[/TD]
[TD]25/02/2016[/TD]
[TD]29/02/2016[/TD]
[TD]01/03/2016[/TD]
[/TR]
[TR]
[TD]6867668[/TD]
[TD]19/02/2015[/TD]
[TD]waiting for missing documents[/TD]
[TD]16534356[/TD]
[TD]19/07/2015[/TD]
[TD]25/08/2015[/TD]
[TD]01/03/2016[/TD]
[/TR]
[TR]
[TD]6867668[/TD]
[TD]19/02/2015[/TD]
[TD]missing/false documents[/TD]
[TD]16534356[/TD]
[TD]12/07/2015[/TD]
[TD]19/07/2015[/TD]
[TD]01/03/2016[/TD]
[/TR]
[TR]
[TD]6976458[/TD]
[TD]22/03/2015[/TD]
[TD]cash request[/TD]
[TD]50649391[/TD]
[TD]08/02/2016[/TD]
[TD]09/02/2016[/TD]
[TD]15/02/2016[/TD]
[/TR]
[TR]
[TD]7164340[/TD]
[TD]11/05/2015[/TD]
[TD]waiting for missing documents[/TD]
[TD]57208340[/TD]
[TD]11/05/2015[/TD]
[TD]26/08/2015[/TD]
[TD]03/01/2016[/TD]
[/TR]
[TR]
[TD]7259512[/TD]
[TD]09/06/2015[/TD]
[TD]waiting for missing documents[/TD]
[TD]13195672[/TD]
[TD]04/01/2016[/TD]
[TD]21/02/2016[/TD]
[TD]23/02/2016[/TD]
[/TR]
[TR]
[TD]7421034[/TD]
[TD]04/08/2015[/TD]
[TD]waiting for missing documents[/TD]
[TD]24888638[/TD]
[TD]04/08/2015[/TD]
[TD]02/09/2015[/TD]
[TD]11/01/2016[/TD]
[/TR]
[TR]
[TD]7483311[/TD]
[TD]27/08/2015[/TD]
[TD]missing/false documents[/TD]
[TD]1754621[/TD]
[TD]04/01/2016[/TD]
[TD]05/01/2016[/TD]
[TD]10/01/2016[/TD]
[/TR]
[TR]
[TD]7505991[/TD]
[TD]06/09/2015[/TD]
[TD]missing/false documents[/TD]
[TD]17274630[/TD]
[TD]05/01/2016[/TD]
[TD]17/03/2016[/TD]
[TD]12/04/2016[/TD]
[/TR]
</tbody>[/TABLE]



i need to find the time it takes to deal with application ,

the conditions are like this:
if (G3-B3)<=90
then application time is the result of : application number End date - application number start date

but if
(G3-B3)>90

then the calculation should be the

[TABLE="width: 1295"]
<tbody>[TR]
[TD]application number End date for task name "missing / false documents" or for task name " waiting for missing documents" ( i have to pick one of them according to the latest)

minus


[TABLE="width: 1295"]
<tbody>[TR]
[TD]task end date

thank you![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1295"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

[TABLE="class: cms_table, width: 1295"]
<tbody>[TR]
[TD]6976458[/TD]
[TD]22/03/2015[/TD]
[TD]cash request[/TD]
[TD]50649391[/TD]
[TD]08/02/2016[/TD]
[TD]09/02/2016

[/TD]
[TD]15/02/2016[/TD]
[/TR]
</tbody>[/TABLE]
how is the application for a cash request 22/3/15 then 8/2/16 and 9/2/16 and 15/2/16

 

[jhonatanbenami]

there has been an error while copying :

here is correct line for it:

[TABLE="width: 1295"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]A[/TD]
[TD="align: left"]B[/TD]
[TD="align: left"]C[/TD]
[TD="align: left"]D[/TD]
[TD="align: left"]E[/TD]
[TD="align: left"]F[/TD]
[TD="align: left"]G[/TD]
[/TR]
[TR]
[TD]application number[/TD]
[TD]application number start date[/TD]
[TD]task name[/TD]
[TD]ID[/TD]
[TD]task start date[/TD]
[TD]taske end date[/TD]
[TD]application number End date[/TD]
[/TR]
[TR]
[TD]6976458[/TD]
[TD]22/03/2015[/TD]
[TD]cash request[/TD]
[TD]50649391[/TD]
[TD]08/02/2016[/TD]
[TD]09/02/2016[/TD]
[TD]15/04/2016

[/TD]
[/TR]
</tbody>[/TABLE]

an application can have different tasks....

 

[oldbrewer]

sorry - I do not understand what you want - is application number a customer number ?

 

[jhonatanbenami]

you can refer to it like a customer number

 

[oldbrewer]

[TABLE="width: 1183"]
<colgroup><col><col><col><col span="2"><col><col><col span="2"></colgroup><tbody>[TR]
[TD]customer[/TD]
[TD]date requested[/TD]
[TD]task name[/TD]
[TD]customer ID[/TD]
[TD]task start date[/TD]
[TD]task end date[/TD]
[TD]confirmed as COMPLETE[/TD]
[TD]TIME[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6256372[/TD]
[TD="align: right"]16/07/2014[/TD]
[TD]missing/false documents[/TD]
[TD="align: right"]63298632[/TD]
[TD="align: right"]19/01/2016[/TD]
[TD="align: right"]19/01/2016[/TD]
[TD="align: right"]26/01/2016[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6256372[/TD]
[TD="align: right"]16/07/2014[/TD]
[TD]cash request[/TD]
[TD="align: right"]63298632[/TD]
[TD="align: right"]03/01/2016[/TD]
[TD="align: right"]20/01/2016[/TD]
[TD="align: right"]26/01/2016[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6715072[/TD]
[TD="align: right"]08/01/2015[/TD]
[TD]missing/false documents[/TD]
[TD="align: right"]314019381[/TD]
[TD="align: right"]28/10/2015[/TD]
[TD="align: right"]21/01/2016[/TD]
[TD="align: right"]25/01/2016[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6867668[/TD]
[TD="align: right"]19/02/2015[/TD]
[TD]missing/false documents[/TD]
[TD="align: right"]16534356[/TD]
[TD="align: right"]25/02/2016[/TD]
[TD="align: right"]29/02/2016[/TD]
[TD="align: right"]01/03/2016[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6867668[/TD]
[TD="align: right"]19/02/2015[/TD]
[TD]waiting for missing documents[/TD]
[TD="align: right"]16534356[/TD]
[TD="align: right"]19/07/2015[/TD]
[TD="align: right"]25/08/2015[/TD]
[TD="align: right"]01/03/2016[/TD]
[TD="align: right"]189[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6867668[/TD]
[TD="align: right"]19/02/2015[/TD]
[TD]missing/false documents[/TD]
[TD="align: right"]16534356[/TD]
[TD="align: right"]12/07/2015[/TD]
[TD="align: right"]19/07/2015[/TD]
[TD="align: right"]01/03/2016[/TD]
[TD="align: right"]226[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6976458[/TD]
[TD="align: right"]22/03/2015[/TD]
[TD]cash request[/TD]
[TD="align: right"]50649391[/TD]
[TD="align: right"]08/02/2016[/TD]
[TD="align: right"]09/02/2016[/TD]
[TD="align: right"]15/04/2016[/TD]
[TD="align: right"]66[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7164340[/TD]
[TD="align: right"]11/05/2015[/TD]
[TD]waiting for missing documents[/TD]
[TD="align: right"]57208340[/TD]
[TD="align: right"]11/05/2015[/TD]
[TD="align: right"]26/08/2015[/TD]
[TD="align: right"]03/01/2016[/TD]
[TD="align: right"]130[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7259512[/TD]
[TD="align: right"]09/06/2015[/TD]
[TD]waiting for missing documents[/TD]
[TD="align: right"]13195672[/TD]
[TD="align: right"]04/01/2016[/TD]
[TD="align: right"]21/02/2016[/TD]
[TD="align: right"]23/02/2016[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7421034[/TD]
[TD="align: right"]04/08/2015[/TD]
[TD]waiting for missing documents[/TD]
[TD="align: right"]24888638[/TD]
[TD="align: right"]04/08/2015[/TD]
[TD="align: right"]02/09/2015[/TD]
[TD="align: right"]11/01/2016[/TD]
[TD="align: right"]131[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7483311[/TD]
[TD="align: right"]27/08/2015[/TD]
[TD]missing/false documents[/TD]
[TD="align: right"]1754621[/TD]
[TD="align: right"]04/01/2016[/TD]
[TD="align: right"]05/01/2016[/TD]
[TD="align: right"]10/01/2016[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7505991[/TD]
[TD="align: right"]06/09/2015[/TD]
[TD]missing/false documents[/TD]
[TD="align: right"]17274630[/TD]
[TD="align: right"]05/01/2016[/TD]
[TD="align: right"]17/03/2016[/TD]
[TD="align: right"]12/04/2016[/TD]
[TD="align: right"]26[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]are these the answers you are looking for in the last column[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jhonatanbenami]

Yes!

 

[jhonatanbenami]

would you mind sharing the knowledge ?

 

[oldbrewer]

for the first answer the formula I used was =if(g3-b3<=90,g3-b3,g3-f3)