Comparing Dates

[John]

I am working on a project to track parts ordered, shipped, and recieved. I am
trying to calculate the number of days between the entries automatically. I
have the following:

d1=date aircraft grounded
d2=date order generated
d3=date part shipped
d4=date unit recieved part
d5=date aircraft back in service
c1=compare d2-d1
c2=compare d3-d2
c3=compare d4-d3
c4=d5-d1, (total days aircraft grounded)

my first comparision is the difference between date 2 and date 1;d2-d1=c1,
but until i put in a date into d2 i get a large negative number. how can i
get it to not compute the difference until both dates are entered?

there may be as many as three or four days between the entries. is it
possible to have the cell or line change color if the number of days exceeds
four days?

i have the same problem with all of the comparisions. there are occasions
when an order will be cancelled prior to shipping and i need to enter "N/A"
inot the cell.

 

[Mike H]

Hi,

Try this

=IF(COUNT(D12)=2,D2-D1,"")

For colouring the cells use conditional formatting

Mike

 

[John]

Hi Mike H,

I tried your suggestion and got the same result. Since I posted the question
my boss changed the layout. The updated infor is:

I am working on a project to track parts ordered, shipped, and recieved. I am
trying to calculate the number of days between the entries automatically. I
have the following:

C12=date aircraft grounded
G12=date order generated
J12=date part shipped
M12=date unit received part
O12=date aircraft back in service
H12=compare G12-C12
K12=compare J12-G12
N12=compare M12-J12
P12=compare O12-M12, (total days aircraft grounded)

I still get a large negative number until i enter the second date.

????

 

[John]

Hi Mike,

I have tried changing the formula several different ways with no results and
I am back at your original formula.

How do I keep it from doing the calculation until both dates that need to be
compared are entered?

John

 

[John]

To All,

After playing with the formula and researching other formulas I think I
stumbled on the solution.

=IF(G12="","",IF(G12="N/A", "N/A",IF(G12<>"",G12-C12)))

first I compared to see if the cell held a blank; then I compared to see if
the cell it held "N/A"; third I compared to if it held anyting other than a
blank or N/A, then do the calculation.

A bit complicated and confusing to follow at first but it is working so far,
if anyone know of an easier way please let me know.

Have a good day!