Rebates paid early or late - how many days?

[colans]

I've attached a workbook containing data and pivots. My organization is trying to identify how much we could have saved (if anything) by paying customer rebates according to the contract rather than paying earlier (or later) than contract terms. I show in the pivots that we would have paid close to $15M less in 2017. The next question being asked is to show how much was paid 1 day early, 5 days early, 20 days late etc.
The catch is, even if it was paid 1 day early but falls within the same month, it shouldn't be counted.
Hope this makes sense. Any help is greatly appreciated!

 

[GraH - Guido]

Hi colans,
I have some trouble understanding your data. Is this a correct reading:
- Act Days Paid seems to be the difference between Receive Date and Paid Date?
- The Pay Day is the term you need to respect?
- If Act Days Paid < Pay day, you paid too early and this is the data you are after?
- If this difference is 1 day and falls in same month then should not be in scope.
- Else you want to see this data grouped in kind of buckets (1 d; 5d; 20d)
- I do not need to consider all columns with ACT and New in the labels.

 

[colans]

yes, that is correct

 

[GraH - Guido]

yes, that is correct

Then I believe the file I attached contains a possible solution.
I now added a pivot. You can adapt the range "buckets" as you need them to be.

 

[colans]

Your solution is not exact but has given me some ideas to work with, thank you!

 

[GraH - Guido]

Your solution is not exact but has given me some ideas to work with, thank you!

Still mission accomplished then ;-). Always difficult to handle and understand requirements... I'm glad that I helped somehow.