Corresponding Values with Dates

[antmorano]

I have a worksheet in which I have a row with number 1-12 to
represent
months. I a column which contains dates of termination in "mm/dd/yy"
format. I am trying to create a formula which looks at the month
within the date of the termination column and corresponds that with
the correct month in the first row with 1-12. I then want it to
count
number of "0's" between the last payment that was given and when the
last payment should have been given. For example if the term date is
06/12/06 then it should correspond with the "6" number in the top
row. But if payments only came up to 04/2006 then they owe two
payments for two more months. This is what I have so far and I'm not
sure if it works. Any input is appreciated.

=IF(J:J="",COUNTIF(K2:V2,"0")*58,"AUDIT",IF(J:J="mm/dd/
yy",INDEX(K1:V1,MATCH("mm",J2,0),1)))


-Anthony Morano

 

[T. Valko]

How do you denote if a payment was "given" ?

See if this screencap gives you an idea:

http://img74.imageshack.us/img74/4329/formulayq6.jpg

 

[T. Valko]

Ugh! I forgot to have the formula cell selected so that it would show the
formula.

This is the formula entered in cell O2:

=MONTH(A2)-IF(COUNT(B2:M2),MATCH(10^10,B2:M2),0)

Copied down and formatted as GENERAL

 

[antmorano]

A payment is denoted if there is a whole value in the field. This
info was first cross referenced in Access. If there is a value in the
cell it would be like 58... if there is no value then it turns up as a
0. I haven't tried your formula yet, b/c I am not in my office right
now. From the results that I see in your screen shot it should work
for me. The only difference is the "0"'s where there are no values
and the fact that i would like for that number to then be multiplied
by a fixed value. But I truly appreciated the response.

 

[T. Valko]

Ok, will there be any 0s for the months that exceed the term date?

For example, if the term date is 7/1/2007 do the cells for months 8-12 have
0s or are the empty?

This should be relatively easy to do so the more info you can provide the
better.

 

[antmorano]

Morning Biff- There are "0"'s for any cell which does not contain a
value. Even if the cell was not supposed to contain hours there would
still be no 0's. I'm going to try your formula now to see how it
turns out.

 

[antmorano]

Biff- The result I'm getting are all ######## in the cell. Here is
what I have as the formula.

=MONTH(J2)-IF(COUNT(K2:V2),MATCH(10^10,K2:V2),0)

Let me know what you think.

-Anthony Morano

 

[antmorano]

I was able to get something to work... here is the new formula. I now
get a negative number for some of my results. I'm assumin it has to
do with all the zeros.

=IF(J4<>"",MONTH(J4)-IF(COUNT(K4:V4),MATCH(10^10,K4:V4),0))


-Anthony Morano

 

[T. Valko]

See this screencap:

http://img50.imageshack.us/img50/4982/count0kb4.jpg

If that doesn't work then I'll need to see how your data is setup.

 

[antmorano]

Biff- This worked great!... Thanks so much. I don't know if you know
how to do this but I am trying to do an autofilter now under the
custom option to show me only the year 2006 termination dates. Do you
know how to do that b/c I'm having a real hard time attempting this.

-Anthony Morano

 

[T. Valko]

Assume column J are your dates.

Select CUSTOM from the date drop down

Criteria:

is greater than or equal to ..... 1/1/2006
and
is less than or equal to ..... 12/31/2006
OK