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?

 

[MrTeeny]

=DATEDIF(A1, B1, "d") will give the difference in days between A1 and B1

To add a month to a date you can use =EDATE(A1,1) So stick first month in A1 and =EDATE(A1,1) in A2 and drag the formula down to A12. Then stick =IF(B1<>"",DATEDIF(A1, B1, "d"),"") in cell C1 and drag it down to C12, use Column B to enter actual date received

 

[James006]

Hello,

You can test following :

=SUMPRODUCT(--(DAY(A1:A12)>15))

HTH

 

[Stan Dad]

Many thanks James - this works a treat.

Hello,

You can test following :

=SUMPRODUCT(--(DAY(A1:A12)>15))

HTH

 

[Stan Dad]

Thanks Mr Teeny; however I only have 1 cell to place the formula and James solution does just that.

=DATEDIF(A1, B1, "d") will give the difference in days between A1 and B1

To add a month to a date you can use =EDATE(A1,1) So stick first month in A1 and =EDATE(A1,1) in A2 and drag the formula down to A12. Then stick =IF(B1<>"",DATEDIF(A1, B1, "d"),"") in cell C1 and drag it down to C12, use Column B to enter actual date received

 

[MrTeeny]

No problem I'd actually misread what you were asking for good to see James' solution works for you

 

[James006]

Many thanks James - this works a treat.

You are welcome ...

 

[Stan Dad]

Hi all. I’m back for a second bite at this cherry.

James’ formula works only to the end of the month - if the payment is made early in the following month, it isn’t recognised as being late.

Also, to declutter my spreadsheet, I’ve used the formula, IF(A12>TODAY(),””,A12), to blank out future dates; however SUMPRODUCT interprets these cells as data, not numbers and returns a #VALUE! error.

A solution to either would be much appreciated.

 

[James006]

Hello,

In your 12 cells span ... how many different months can you have ?

To make your query work, is there a cell where you are inpuing the month for which you need the calculation to be made ...?

 

[Stan Dad]

Cheers James. Cells A1: A12 contain the paydates for the 12 months - A1, 15/01; A2, 15/02; etc. If a payment is made on a different date, I simply overtype the contents with the actual paydate (e.g. 31/10 in cell A10). So in theory, any of the 12 cells could contain any date, if the payment is thaaaaaat late.