Formula help for days past due

[lkndllsgrl]

I am trying to use a countif formula to determine how many dates were on time, 7 days late, 14 days late and 30 days or more late. You can see in the table below the columns I am working with. [TABLE="width: 500"]
<tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Date Recieved[/TD]
[TD]Date Due[/TD]
[TD]Date Submitted[/TD]
[/TR]
[TR]
[TD]10/27/17[/TD]
[TD]11/26/17[/TD]
[TD]12/27/17[/TD]
[/TR]
[TR]
[TD]10/18/17[/TD]
[TD]11/17/17[/TD]
[TD]11/13/17[/TD]
[/TR]
[TR]
[TD]10/17/17[/TD]
[TD]10/23/17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]On Time[/TD]
[TD]7 Days Late[/TD]
[/TR]
[TR]
[TD]10/1/17[/TD]
[TD]10/31/17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/1/17[/TD]
[TD]11/30/17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The current formula I have is

=countifs(C:C,”>="&A1,C:C,"<="&B1,E:E,”<=”&D:D)

The first part of the formula brings back the correct count of number of instances within the date frame, howver I cannot get the second part to return count of on time instances. \

Please help

 

[ajamess]

I would make a helper column, which either shows how many days late it was, or uses ifs to label each row with what you're looking for "7 days", "14 days". This will make your countifs much easier.

 

[lkndllsgrl]

I did try a helper column with days late, but it acts funny when no dates are input yet. I wasnt sure of a work around for that so I abandoned that train of thought.

 

[ajamess]

do something for your helper column like:

=if(and(Date1Cell <> "", Date2Cell <> ""), Date2Cell - Date1Cel, "")

Then when no dates are input, it will just be blank, otherwise calculate the days late.

 

[lkndllsgrl]

That worked! Now to get the rest of the countifs to work.

 

[ajamess]

I would actually probably do two helper columns (Just because I like to keep my formulas shorter). In second helper column I would do something like:

=if(helpercolumn <= 0, "On Time", if(helpercolumn <=7, "7 days late"....)

To label each row.

Then on your dashboard do something like,

=countifs(DateReceivedColumn, ">=" & $A2, DateReceivedColumn, "<=" & $B2, HelperColumn2, F$1)

 

[lkndllsgrl]

I just used the one helper column and it worked well. A bit complex but I got it. Thank you for your help.

I could take this one step further and count the blanks if the due date is past todays date. What do you think of that?

 

[ajamess]

Do you mean if todays date is past the due date?

I think that would be a good idea.

I don't know the exact structure of your workbook but something like:

=if(DueDateCell = "", "", if(DateReceivedCell = "", Today() , DateReceived)-DueDateCell)

 

[ajamess]

The above would be how many days it is past due, as of now.

If you just want to count, number of "Open" past dues (Past Dues that still haven't come in). could just count where DueDate <= Today, and received is still blank.

 

[lkndllsgrl]

how could i incorporate the the blank past due into the count I have already established? Another helper column?