Average days to pay invoice.

%3CLINGO-SUB%20id%3D%22lingo-sub-2158801%22%20slang%3D%22en-US%22%3EAverage%20days%20to%20pay%20invoice.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2158801%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%3C%2FP%3E%3CP%3ESo%20I%20have%20this%20task%20where%20I%20need%20to%20calculate%20after%20how%20many%20days%20on%20average%20each%20company%20pays%20their%20bills.%20Between%20the%20set%20date%20to%20pay%20the%20invoice%20and%20actual%20day%20when%20it's%20paid%20for%20each%20company.%20C%20collumn%20is%20date%20set%20to%20pay%20invoice%2C%20D%20is%20actual%20date%20when%20invoice%20was%20paid%20and%20E%20is%20companies.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2158801%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2158839%22%20slang%3D%22en-US%22%3ERe%3A%20Average%20days%20to%20pay%20invoice.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2158839%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F975852%22%20target%3D%22_blank%22%3E%40linards2000%3C%2FA%3E%26nbsp%3BSomething%20like%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20did%20date%20due%20(column%20C)-date%20paid%20(column%20D).%20That%20will%20give%20me%20the%20the%20days%20paid%20per%20sale%2Finvoice.%20Positive%20being%20paid%20before%20and%20negative%20being%20days%20late.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20created%20a%20Pivot%20Table%20with%20the%20companies%20(column%20E)%20in%20the%20rows%20and%20the%20new%20calculation%20in%20the%20values%20and%20formatted%20to%20be%20an%20average.%20If%20you%20look%20at%20screenshot%20with%20the%20data%20you'll%20see%20the%20average%20matches.%20You%20could%20also%20format%20the%20pivot%20value%20as%20a%20whole%20number%20if%20you%20need%20a%20rounded%20value%20as%20in%20a%20whole%20day.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20even%20see%20at%20the%20invoice%20level%20by%20adding%20in%20column%20A%20and%20then%20you%20can%20see%20how%20it%20will%20average%20with%20the%20total%20averaging%20in%20the%20subtotal.%20This%20isn't%20what%20you%20asked%20but%20you%20can%20from%20there%20customize%20the%20data%20and%20even%20search%20for%20a%20company%20via%20the%20filter%20in%20the%20Pivot.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2158848%22%20slang%3D%22en-US%22%3ERe%3A%20Average%20days%20to%20pay%20invoice.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2158848%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F975852%22%20target%3D%22_blank%22%3E%40linards2000%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20use%20a%20pivot%20table.%3C%2FP%3E%0A%3CP%3EI%20added%20a%20column%20Dien%C5%B3%20skai%C4%8Dius%20to%20the%20source%20data%20with%20formula%20%3DD2-C2.%3C%2FP%3E%0A%3CP%3EThe%20pivot%20table%20calculates%20the%20average%20of%20this%20column%20for%20each%20company.%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello.

So I have this task where I need to calculate after how many days on average each company pays their bills. Between the set date to pay the invoice and actual day when it's paid for each company. C collumn is date set to pay invoice, D is actual date when invoice was paid and E is companies.

6 Replies

@linards2000 Something like this?

 

I just did date due (column C)-date paid (column D). That will give me the the days paid per sale/invoice. Positive being paid before and negative being days late.

 

Then created a Pivot Table with the companies (column E) in the rows and the new calculation in the values and formatted to be an average. If you look at screenshot with the data you'll see the average matches. You could also format the pivot value as a whole number if you need a rounded value as in a whole day. 

 

You can even see at the invoice level by adding in column A and then you can see how it will average with the total averaging in the subtotal. This isn't what you asked but you can from there customize the data and even search for a company via the filter in the Pivot.

@linards2000 

I'd use a pivot table.

I added a column Dienų skaičius to the source data with formula =D2-C2.

The pivot table calculates the average of this column for each company.

See the attached version.

Super. Thank you so much. @Jpalaci1 

Amazing. Thanks!

@linards2000 

image.png

with number format

[Red]0 "days late ";[Color10]0 "days early";"On time"

@linards2000 

 

Please see attached file