Date Formula

[reymon2012]

Good Evening All, In need of assistance for a date formula.

In cell A2 I have date value for Anticipated Complete Date (ACD), B2 date value for start date (SD) and C2 date value for Completed date (CD).
Based on the given value above I need a formula that will return a value on Cell D and E based on the following criteria.

Cell D = if difference between two dates for A2 and C2 is within 5 days before complete date it will return a value of “1” , otherwise value is “0”
Cell E = if difference between two dates for C2 and B2 is <=1 day it will return a value of “0”, otherwise “1”.

Any help is much appreciated.

 

[gitmeto]

=if(A2-C2<5,1,0)

 

[Jamie McMillan]

Hello,

I assume you meant A2 and B2 "Cell D = if difference between two dates for A2 and C2" as C2 is the end date. I used B2; but you can change that.

Cell D1 =IF(DAYS(B2,A2)+DAYS(C2,B2)<=5,1,0)
Cell E1 =IF( DAYS(C2,B2)<=1,0,1)

Jamie

 

[Jamie McMillan]

Hello Gitmeto,

=if(A2-C2<5,1,0) A2-C2 will be a negative number.

And Excel gets a little bit messed up using dates, as the associated numbers, are seconds of a day.



Jamie

 

[gitmeto]

Maybe I have the order incorrect but I typed two dates in two different columns then applied the formula. Maybe try changing the data type to short date then you won't have to deal with the minutes, seconds, etc.

 

[reymon2012]

Hello,

I assume you meant A2 and B2 "Cell D = if difference between two dates for A2 and C2" as C2 is the end date. I used B2; but you can change that.

Cell D1 =IF(DAYS(B2,A2)+DAYS(C2,B2)<=5,1,0)
Cell E1 =IF( DAYS(C2,B2)<=1,0,1)

Jamie

Appreciate the reply Jamie.

Regarding Cell D value. Its A2-C2 and NOT B2-C2.

 

[reymon2012]

=if(A2-C2<5,1,0)

Thanks gitmeto. !!