Difference between dates

NLegresley

Hello

I am looking to pull some data from a quote log we have made up. 

What I need to know is what quotes were late submitting by 1-2 days and which were late submitting by 2+ days, so two data points.

Attached is a screenshot of the columns we have. Requested due date is when our customer expected the quote and the date submitted is when we actually sent the quote to them. 

I would like to know how many quotes were overdue for each criteria and possibly a percentage of late quotes for each criteria. 

In the end I am looking to put these metrics on our dashboard.

Any help with appropriate formulas is appreciated.

Thanks

 

Paul Newcome

=[Date Submitted]@row - [Requested Due Date]@row

 

will give you how many days are in between. A positive number means it was late, zero means on time, and a negative number denotes early.

.

You can then use a COUNTIFS to find out how many were late by x number of days.

 

=COUNTIFS([Date Difference]:[Date Difference], @cell > 2)

 

will give you how many were more than 2 days late.

 

=COUNTIFS([Date Difference]:[Date Difference], OR(@cell = 1, @cell = 2))

 

will give you how many were 1 - 2 days late.

.

To formulate your percentage, simply take the count from above and divide it by the total number of entries.

 

=COUNTIFS([Date Difference]:[Date Difference], @cell > 2) / COUNTIFS([Requested Due Date]:[Requested Due Date], ISDATE(@cell))

 

will give you the percentage of those that were more than 2 days late.

 

=COUNTIFS([Date Difference]:[Date Difference], OR(@cell = 1, @cell = 2))

/ COUNTIFS([Requested Due Date]:[Requested Due Date], ISDATE(@cell))

 

will give you the percentage for 1 - 2 days late.

NLegresley

So I have a separate sheet called Data where I have my formulas populate to. On that sheet is where I put the =COUNTIFS formulas? I need to reference another sheet with these formulas. 

Here is what I have but it is unparsable

=COUNTIFS([{TVS Quote Log 2019 Range 6}]:[{TVS Quote Log 2019 Range 6}], @cell > 2)

=COUNTIFS([{TVS Quote Log 2019 Range 6}]:[{TVS Quote Log 2019 Range 6}], OR(@cell = 1, @cell = 2))

Paul Newcome

When referencing another sheet, the range that is in the { } is whatever you selected. Therefore, if you selected an entire column, then that one cross sheet reference refers to the entire column. You don't have to repeat it like you would a column reference on the same sheet as the formula.

 

Also... The square brackets [ ] are only used when referencing a column on the same sheet that has a space, special character, or number in the name. Cross sheet references use ONLY { }.

 

=COUNTIFS({TVS Quote Log 2019 Range 6}, @cell > 2)

=COUNTIFS({TVS Quote Log 2019 Range 6}, OR(@cell = 1, @cell = 2))

NLegresley

That's perfect, everything worked

Thanks for all the help. 

NLegresley

One last request. 

 

Is there a way to change a cell that is a drop down list to expired based on the date? Our quote validity is 90 days so we want the sheet to automatically expire a quote if we exceed 90 days so the quote isn't open in the quote log. 

We also need the cell to be functional as the drop down for the other criteria. 

If this all isn't possible no big deal, just trying to automate something we have to do manually. 

Paul Newcome

If you automate 1, you will want to automate them all. If you have a formula in a cell and then manually change the dropdown selection, you will essentially override and delete the formula.

NLegresley

I've discovered an issue with this formula possibly. When I use 

=COUNTIFS({Production Schedule Range 2}, @cell > 1)

I am getting a count of 65 but the actual count should be 19. This is a count of late orders. I want it to count if the order is 1 or more days late and not count 0 values. 

Am I missing something?

Paul Newcome

What data is in the range?

NLegresley

Data is a cheater column that counts the difference of days between 2 dates using this formula:

 

=[Ship Date]@row - [Customer Req. Date]@row

Paul Newcome

You are going to want to check your counts then. The only issue I see with your formula is that you are using @cell > 1 instead of @cell >= 1 to count for cells that are greater than or equal to 1.

.

Try building a filter on the data sheet to show only rows where that particular column is greater than or equal to 1. Then do a quick count on how many rows met that condition.

NLegresley

I just did another count and it is definitely 19 orders over 1 day or more. 

Total counts is 82 so I think it is counting 0 days or negative days?

NLegresley

Ok I figured it out. We have child lines that someone was putting dates into skewing our count. 

Thanks for all your help on this :P

Paul Newcome

Happy to help. 

 

Glad you were able to get it figured out.

NLegresley

Actually I do have one more thing on this. 

Taking it a step further how would I pull data of the number of orders that are 1 or more days late but by Month.

I am guessing it would be something like this?

=COUNTIFS([Date Shipped], "January", [Days Late], @Cell >=1, IFERROR(MONTH(@cell), 0) = 1)

 

Paul Newcome

Close!

 

=COUNTIFS([Date Shipped]:[Date Shipped], IFERROR(MONTH(@cell), 0) = 1, [Days Late]:[Days Late], @Cell >=1)