question on correct formula to use.

[Guest]

I am having difficulty in creating a comparison formula. I would like to
compare the date that an job was completed with the current date or a set
time ( monthly, weekly, etc.) and then show the result a a number. For
example, if a task was completed on 9/5/04 and it should be done weekly, but
was done 11 days later is it possible to then reflect in another cell a red 3
or a -3.
I appreciate any and all information and help.
Thanks!!!!

 

[icestationzbra]

you could do this either with an 'if' statement, or with conditiona
formatting, or with both.

an 'if' statement could be written as:

=if(CellWhichHasDate<today(),PositiveCondition,NegativeCondition)

and then you could conditionally format for the negative condition t
show up as red in colour.

if your negative condition is always one of some 3 standard value
(such as, yes, not, maybe), the you could even set this condition i
conditional formatting directly.

provide a little more detail as to what are all your possible negativ
conditions, or failure conditions. if 9/5/04 is less than today, the
what? if it is 5 days late, or 15 days late, then what? how is th
failure condition calculated? is it just a subtraction, or is it pulle
out by some other method. as much detail you can provide, would hel
giving a satisfactory resolution

 

[Frank Kabel]

Hi
try something like
=IF(A1-TODAY()>=11,-3,"")

 

[Guest]

Thank you for all of your help.
Here is some more information that might make it clearer:
cell a is date item is done, cell b is due date cell a + either 1 , 3, 7,
30, 165, 365 depending on the frequency that the job is done. That is how I
am determining the due date. The condition that I need help with is showing
if the job was not done on time and went over a day, to show that the job is
overdue by " number" of days it is over due. I understand the if statement
but how could I get it show if it overage ( I am not very familiar with
condition statements). For example if something was to be done this week but
was missed by 6 days how would the formula be modified? I presume from your
post and the previous post = if ( a1-today() <= 7, but how do you show how
many days? I tried the statement but I was able to only get a true false
listed.
Again, thank you all for all of your help. It is really appreciated !!!!

 

[icestationzbra]

you could use the statement like this -:-

in cell C1:

=IF(A1<=B1,0,B1-A1)

or

=IF(A1<=B1,"",B1-A1)

or

=IF(A1<=B1,"Completed",B1-A1)

format C1 as General.

this will provide you the number of days you are lagging behind. modif
A1 and B1 to suit your requirements, i have provided a general formula