## Calculate payment over/shortage

### Calculate payment over/shortage

IN the attached spreadsheet, in column F there is a calculation for the minimum payment due in the following month.
What I've having difficulty with is calculating the amount paid up to and including the 20th day of that month, and then comparing that to the amount that was supposed to be paid, showing what, if any, overage or shortage in payment occurred.
I'd like this in column G.
Since the dates and amounts seemed relevant to solving this, I didn't anonymize that data.

My preference is a solution for OpenOffice, though I do have both Open and LibreOffice installed.
Attachments
CC.ods
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6058
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: Calculate payment over/shortage

RusselB wrote:What I've having difficulty with is calculating the amount paid up to and including the 20th day of that month, and then comparing that to the amount that was supposed to be paid, showing what, if any, overage or shortage in payment occurred.
I don't fully understand how one knows which amounts to include. If it's the amounts in the period to the following 20th of the month =DATE(YEAR(A3);MONTH(A3)+(DAY(A3)>=20);20) and extending back one month =DATE(YEAR(A3);MONTH(A3)+(DAY(A3)>=20)-1;21), based on the date in column A, I'd create auxilllary columns to hold those dates. Then you can use SUMPRODUCT to sum the amounts in (I suppose) column E (\$E\$3:\$E\$201) between those dates. You can include a SUMPRODUCT term to restrict the selection to lines with description "xxxxxx xxx" if that's required for your calculation.
[Tutorial] The SUMPRODUCT function

I do not understand from your attachment how one would know "the amount that was supposed to be paid".
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Moderator

Posts: 3979
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: Calculate payment over/shortage

The amount to be paid would, initially, be the amount shown for minimum payment.
The time period would be from the 21st of the previous month up to and including the 20th of the current billing month.
ie: Sum of negative amounts in E from Jan 21 to Feb 20 would be allocated to the amount in column F with the date of Jan. 26
The date discrepancy is due to processing time by the company.

For my purposes, just seeing if the minimum payment was matched, exceeded or short on a month by month basis, with the start of the month being the same date as that in column A that also has an entry in column F
While that doesn't match the companies processing times exactly, I can work with the discrepancy.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6058
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON