Counting late payment dates

[Stan Dad]

Hi all.

I normally get paid on the 15th of every month, though sometimes this could be later. If I enter the actual payment dates in A1:A12, which formula can I use to count the number of times they were made late?

 

[James006]

Hello again,

Thanks a lot for your explanation .. :wink:

Indeed, it is quite difficult to visualize ... what is exactly in front of Your eyes ...!!!

So ... just go ahead and test following formula :

=SUMPRODUCT(--(A1:A12>DATE(YEAR(A1:A12),ROW(A1:A12),15)))

HTH

 

[James006]

If your December 2017 pay can land in your bank account ... the following year ....

1. You should quit .... !!! :wink:

2. and use this formula

=SUMPRODUCT(--($A$1:$A$12>DATE(2017,ROW($A$1:$A$12),15)))

Feel free to test ... and comment ...

 

[Stan Dad]

You're the real deal, James!!!

Yes, this works - boy, does it just! I've tested it for dates well into the future and it holds good. Well done, James. When I blank out future dates, using <a12,"",a12) need.
IF(TODAY()<A12,"",A12), it treats the blanks as missed payment dates, but I can use COUNTBLANK to deduct these from the result. So I might end up with an unwieldy formula, but it works.
Nice one, James. </a12,"",a12)>

 

[James006]

Glad you can finally rely on ... a solution ...

Thanks a lot .. for your Thanks ...

and for the Beer ... !!! ...

Cheers

 

[Stan Dad]

the rest of the message reads:

<a12,"",a12), I="" it="" deduct="" to="" COUNTBLANK="" use="" can="" but="" payments,="" as="" the="" from="" these="" treats="" total.
<a12,"",a12), I="" it="" deduct="" to="" COUNTBLANK="" use="" can="" but="" payments,="" as="" the="" from="" these="" treats="" total.
<a12,"",a12) need.=""><a12,"",a12), I="" it="" deduct="" to="" COUNTBLANK="" use="" can="" but="" payments,="" as="" the="" from="" these="" treats="" total.
It treats blank cells as missed payments but I can use COUNTBLANK to deduct those

<a12,"",a12), it="" up="" end="" deduct="" to="" use="" can="" but="" missed="" as="" blanks="" the="" works.="" countblank="" i="">Nice one, James. </a12,"",a12),></a12,"",a12),></a12,"",a12)></a12,"",a12),></a12,"",a12),>