Excel count the number of times a date is overdue

[Unlucky]

I'm stumped with trying to figure out how to determine the number of times multiple documents are overdue within a specific time period. I have a document issue date (Column A), and a document due date (Column B), that I need to group into monthly reports (e.g. how many were late this month), in a third column (Column C). The worksheet is broken down into calendar months to display a trend over time. The required time to return the documents is 14 days. The columns are 250 rows long. Does anyone have a formula I can use?

 

[bbott]

Welcome to the board.

I'm assuming that this is just for one calendar year? Enter this formula in C1. It will give the count of overdue documents for January. Copy it down to C12 to get the rest of the months.

=SUMPRODUCT(--($B$1:$B$250-$A$1:$A$250>14),--(MONTH($A$1:$A$250)=ROW(A1)))

 

[Unlucky]

It actually covers three fiscal years, for trending purposes; however, copying the formula into each month cell should work just fine.

However, it didn't work. The cell displays #VALUE!

I understand the logic of the formula, with the exception of "=ROW(A1)))"

 

[Stamoulis]

could use the DateDif Function to find the days between 2 dates .
Syntax: =DateDif(start date, end date , interval) , where interval type : "d"-for days, "y"- for years, "m"- for months.
Having the intervals use the formula " =IF(H2>14,"Overdue"," ") " in the 3rd column to find which documents are overdue.
Then in a cell use the formula " =COUNTIF(D2:D4,"Overdue") " to find the total number of documents that are overdue.
Please , let me know if that worked for you .

 

[Unlucky]

Looks like it works now. Not sure what happened, but it's good. Thank you very much.

 

[bbott]

It actually covers three fiscal years, for trending purposes

Do you need to separate years, or do you just want "All overdue for January", etc?

However, it didn't work. The cell displays #VALUE!

Are your dates entered as text?

I understand the logic of the formula, with the exception of "=ROW(A1)))"

The MONTH function returns the month number of the date (i.e. 1/14/14 would return 1 for January) and the ROW function returns the row number. Setting MONTH() = ROW() within SUMPRODUCT() in C1 is equivalent to saying "Sum each cell where the month number = the current row number." So the formula in row 1 sums Jan, row 2 sums Feb, etc...

 

[Unlucky]

Three fiscal years (starting with October and ending in September) are important. What would I need to do with the formula to recognize each fiscal year, since A1 (for January) would only work once?

The #VALUE! is gone - all is well!

 

[bbott]

Since you're using a fiscal year and not a standard calendar year, the formula's a bit longer. My suggestion would be to place the start and end dates of each fiscal year into cells and reference those. For example, if the start of your first year was in G1 and the end of your first year was in H1, the formula would look like:

=SUMPRODUCT(--($B$1:$B$250-$A$1:$A$250>14),--(MONTH($A$1:$A$250)=ROW(A1)),--($A$1:$A$250>=$G$1),--($A$1:$A$250<$H$1))

You could then copy the formula into columns D and E for the subsequent years and just change the cell references as needed.

 

[Unlucky]

Unfortunately it didn't work. I used column A & B in the original post because it was easier to describe. In actuality, Column K has the 'due date' and Column V has the 'received date' (as shown in the formula below. I used =ROW(A10) for October.


=SUMPRODUCT(--($V$5:$V$254-$K$5:$K$254>14),--(MONTH($K$5:$K$254)=ROW(A10)),--($K$5:$K$254>=$AP$5),--($K$5:$K$254<$AP$16))


Cell AP5 (October) and cell AP16 (September) are the start and end of the fiscal year I only have the three letter abbreviation for the month; I assumed this would work.

Entering data in those cells I have a document that is 55 days overdue, but the formula in Column AX shows '0' as the total (quantity of overdue items). Any ideas?

 

[Unlucky]

I added another column titled "overdue", to calculate the 14 day time period using =IF(AL5>14,"OVERDUE"," ") (Column AL uses the formula =DATEDIF(K5,W5,"D") ) and then used
=COUNTIFS($L$5:$L$254,"=OVERDUE",$J$5:$J$254,">=10/1/2014",$J$5:$J$254,"<=10/31/2014") for the monthly total. Funny thing is, it works.

I genuinely appreciate all your help, and all the time you spent trying to help. Thank you very much! Take care!