I found that jenop2's response from 3/28/2023 is correct: in this report, the payment(s) for the invoice(s) are listed before the invoices. But that didn't get me my date paid, until I started doing some extra work in Excel. (Please read all the way through before completing, you may be able to just use the revised at the bottom.)
- Run the report "Invoices and Received Payments"
- Add a column for Date Paid
- Use this formula in cell I8, which is the first line where I have an invoice:
=IF(C7="Payment",B7,"")
{English translation: If the Transaction Type (Column C) of the row above = Payment, then show the Date field from the line above, if no match, display nothing} - Make your report into a table. Click in cell A5, then press CONTROL+SHIFT+END. (This will highlight all the lines in your report
- Create a table - Insert > Table. Check the box that My table has headers
- Filter your report by transaction Type = Invoice and your new column for payment date = Blank
(You can also filter A/R Paid =Paid) - These are the invoices that have been paid, but were paid with a payment applied to multiple invoices. I filled the blank payment date column with a color, then removed the filters. Now all I have to do is manually review the colored boxes and manually type in the dates.
In my case, this took my 769 line report of 309 invoices to only having to look at 47 records.
Then as I was reviewing these transactions, I made a change to my formula
=IF(C7="Payment",B7,IF(AND(C7="Invoice",NOT(ISBLANK(I7))),I7,""))
{The second IF statement in English reads if the line above's transaction type = Invoice and the line above in the Invoice Date column is not blank, put in that date in this cell.}
Now, it got the correct date for the invoice for almost everything. The only thing this didn't take into account is credit memos used to pay invoices. If you use that second invoice, you don't have to put your report in a table and highlight the missing numbers.
Few extra notes: don't forget to copy and paste special your new column over itself. If your report is resorted or if you delete any lines before doing this, you will lose all your hard work.
If your date field isn't acting like a date field, use Excel's replace command to find a 0 and replace with a 0. This will covert anything that looks like a date into a date. You can also use this to change numbers displaying as text - just search for a . and replace with a . (Yes, I know you can use that help box to do this same thing, but sometimes I get tired of dragging that cursor!)
