Skip to main content
S
suesailor
January 16, 2019
Question

How do I create an average days to pay report for one of our clients?

  • January 16, 2019
  • 7 replies
  • 0 views
Original commenter did not share additional details

    7 replies

    KristyS
    KristyS
    January 16, 2019

    Hi there, swoodrum.

     

    Currently, the report to show the Average Days to Pay report is unavailable in QuickBooks Online. However, I can guide you to prepare a similar report as a workaround. 

    You can send reports to excel to calculate. Note that you can only export a report to excel one at a time.

     

    Here's how you ca do it:

     

    1. From the left menu, select Reports.
    2. Search for and open the report you'd like to view.
    3. Select the Export icon, then choose Export to Excel.
    Also, you can check if there is a third party reporting application that would create this report for you in the Appcenter.
     
    I'll be around if you have any questions about QuickBooks. Have a good one!
    M
    mhwill
    May 15, 2019

    What kind of workaround is this? This is the laziest response that you could have possible given. "We don't have a report to show you the average amount of days it takes for your customers to pay you. But, did you know that you can export a report to Excel?" How does that help? At least tell us which report to run and how to customize it so that we get the information we are looking for, since your dumpster fire of a product doesn't actually provide a streamlined method of managing customer's accounts.

      Angelyn_T
      Angelyn_T
      May 15, 2019

      Hello there, @mhwill.

       

      I'm here to help guide you today so you'll be able to get or pull up the report/s you need.

       

      If you're trying to run a report that shows your customers transactions with payments terms, then you may run the Transaction List by Customer report and make the necessary customization.

       

      Let me show you how:

       

      1. Click on Reports at the left pane.
      2. Select Standard.
      3. Look for the report on the Reports page, or search Transaction List by Customer on the search field.
      4. Customize the date.
      5. Click on the small Gear icon beside the Export icon.
      6. Add the Due date on the reports column.
      7. Click on Run Report to refresh the page.

      If you wish to export the report, simply click on the Export icon and select on Export to Excel.

       

      In addition, you may also check this tutorial to learn more about customizing reports in QuickBooks Online: Customize Reports and Email.

       

      Feel free to add a comment below if you have any other questions about reports in QuickBooks, I'll be always here to help you. Wishing you the best!

      J
      jbowman1611
      June 22, 2019

      Good evening All,

       

      I'm not terribly surprised in QBO's deficiency -- the lack of this report is just another example, but here is my solution:

       

      Three parts are shown: 1) the basic QBO Invoices and payments report (Col A-G), 2) the "Average" calculations (Col I-L), and 3) the formulas used in Col O.

       

      A couple of "helper columns are used to "realign" the payment with the corresponding invoices. The formulas listed in col L, are each copied to the respective columns, then copy & pasted to the bottom of the report -- in my case row 34.

      The first problem is in separating and identifying which lines are payments versus invoices.  The col I & J formulas check and transfer only the Payment number and date, but for each line/invoice to which they apply. Col K formula returns the number of days between the invoice date (Col B and payment date Col J), but only for invoices -- payments always return -1.  Finally (almost), Col L take the average of all payments with the same number (Col I),  but does not include the payment (K= -1) in the average -- this happens only when the payment numbers change [caused by the outer if()].  Finally, (for real now) the bottom of Col L  a standard "Average()" is used to obtain the average days to pay.  Not to be an over achiever (LOL) but since I was here I added Min()/Max() but only on "Invoices", too.

       

      Part of the beauty of this report, is that it can be easily used as a template by running the base report, then copying the report to the left side of the template, there will be some minor clean-up needed on the right side (based on number of rows), but the formulas should only need to be copied/moved down.

       

       

      Hopefully, this will make the formulas easier to work will

      I (eye)IF(C7="Payment",E7,I5)
      JIF(I8=I7,J7,G8) 
      KIF(C7="Invoice",J6-G7,-1)
      LIF(I7=I6,"",AVERAGEIFS(K:K,I:I,I7,K:K, ">=0"))

       

      And the bottom:

      LAVERAGE(L7:L34)
      LMINIFS(F:F,C:C,"Invoice")
      LMAXIFS(F:F,C:C,"Invoice")

       

      Hope that helps.

       

       

        IamjuViel
        IamjuViel
        June 22, 2019

        I am delighted to have you here, @jbowman1611.

         

        I appreciate the steps in generating an average day to pay report that you've shared. The steps will help a lot of customers who are also having the same concern.

         

        Your QuickBooks Online subscription comes with the ability to track income and expenses, send invoices, run payroll and pay and file your taxes. I suggest checking out these articles for tips on how to unlock the software's full potential: QuickBooks Online Help Articles.

         

        You can post anytime if you have questions or want to share your best practices using the product. Have a great rest of your day.

        L
        ldemichele
        August 19, 2020

        I agree. As well this is a must need report. Hopefully it is added only to the "essentials " version of quick books. Please Hurry. Also had an option to put customers on hold. 

         

        Thanks

        Lisa

        J
        Joelo
        July 5, 2021

        Another example of how awful QBO is. I migrated 3 years ago because I had an off-site employee that required access the program and there was no other reasonable way to achieve this. I won't even get into the number of deficiencies this program has compared to desktop. I have 3 pages of stuff that's missing. Intuit made it really easy to migrate to QBO but left no way to bring data back into desktop. No big surprise there.

        Now I have to decide if I want to go back to desktop or a completely new program because QBO is not cutting it for me. 

         

          T
          tstruminger
          February 22, 2022

          3 years later.....still nothing.  But they've increased the price a few times since this question was first posted....

            D
            Dan P11
            January 25, 2024

            Yes, this previous, but currently unavailable functionality is just another feature that has been taken from us in QBO, regardless of subscription level... not that I would pay a dime more for this "unaccounting system". Many of us are looking to run for the exits at this point... Nice job Intuit!

              C
              ChristineJoieR
              January 25, 2024

              I heard your sentiments, @Dan P11.

               

              We are committed to providing the best possible service to our customers, and your feedback is advantageous in helping us achieve this. We are constantly working on updates and improvements to enhance the functionality and user experience of QuickBooks Online.

               

              We appreciate your patience and understanding as we strive to make QuickBooks Online as comprehensive and user-friendly as possible, and we would like to send our heartfelt gratitude for being with us and for using QuickBooks Online.

               

              If you are willing to submit your suggestion, check out the steps below:

               

              1. Go to the Gear icon.
              2. Under the Profile column, select Feedback.
              3. Share your suggestion, then click Next.

               

              You'll want to track your suggestions through this website. You can also visit our blog to keep up with our recent events and developments.

               

              If you have other questions about managing reports, add them below. Please don't hesitate to reach out if you have suggestions or concerns. Your satisfaction is our top priority.

              R
              Rocker252
              June 28, 2024

              This thread was created January 2019.  It is now almost July 2024.  5 1/2 years later and STILL NOTHING has been done to create this report that we are all screaming for. 

               

              Seriously, who do I have to get fired to get this 1 report built?  I know a lot of PE and hedge fund guys who would love to short Intuit stock just to make a few bucks.  Customer negligence is the #1 reason companies fail.  And when Short Sellers smell blood in the water . . .

                Terms & ConditionsAccessibility statement