Corresponding Cells 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

 

[Guest]

I don't understand your formula ...

What is in Column J (dates?)

You cannot check a column using J:J="" .. what are you trying to test?

and here too ...J:J="mm/dd/ yy" ... what are you trying to test?

Perhaps a sample of your data would help.

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