Hello, so I am working on this calculator in Excel and am having some trouble and was wondering if anyone could help. The issue is, in Column C, we need a formula that does a calculation based on date ranges. In short, we need to see if payments adding up to the payment amount (In J1) or larger have been made during after the last due date and before or on the new due date and if not, the fine needs to be the fine amount (J2) * the amount of days between the due dates(column R) and the date of the payment. We also need to make sure that if there are two payments in the same period, the fine does not happen twice.
My thought was, see if the date in column A, was between dates in column R, and if it is, they see if the payment made in column S, was larger than the payment amount, J1, and if it is the same or larger, then the value would be 0, but if it were smaller, take the fine amount, J2, and multiply it by 1+(E1* the number of days between the date, column A, and the due date, Column R). And do all this while if a date that is in between the same range has a value other than 0 in column C, have that second fine amount be 0. If anyone has any ideas it would be greatly appreciated. I understand the basic formula needed I think but am getting tripped up on logic statements of seeing if dates are in the date range. Thank you!
My first thought was a formula like this, =IFS(A6>=$R$2, IF($S$3>=$J$1, "0", (DAYS($R$3,A6)*$E$1)+$J$2), A6>=$R$3, IF($S$4>=$J$1, "0", (DAYS($R$4,A6)*$E$1)+$J$2), A6>=$R$4, IF($S$4>=$J$1, "0", (DAYS($R$5,A6)*$E$1)), A6>=$R$5, IF($S$5>=$J$1, "0", (DAYS($R$6,A6)*$J$2)+$J$2), A6>=$R$6, IF($S$6>=$J$1, "0", (DAYS($R$7,A6)*$E$1)+$J$2), A6>=$R$7, IF($S$7>=$J$1, "0", (DAYS($R$8,A6)*$E$1)+$J$2)), but the values keep returning as 0.
My thought was, see if the date in column A, was between dates in column R, and if it is, they see if the payment made in column S, was larger than the payment amount, J1, and if it is the same or larger, then the value would be 0, but if it were smaller, take the fine amount, J2, and multiply it by 1+(E1* the number of days between the date, column A, and the due date, Column R). And do all this while if a date that is in between the same range has a value other than 0 in column C, have that second fine amount be 0. If anyone has any ideas it would be greatly appreciated. I understand the basic formula needed I think but am getting tripped up on logic statements of seeing if dates are in the date range. Thank you!
My first thought was a formula like this, =IFS(A6>=$R$2, IF($S$3>=$J$1, "0", (DAYS($R$3,A6)*$E$1)+$J$2), A6>=$R$3, IF($S$4>=$J$1, "0", (DAYS($R$4,A6)*$E$1)+$J$2), A6>=$R$4, IF($S$4>=$J$1, "0", (DAYS($R$5,A6)*$E$1)), A6>=$R$5, IF($S$5>=$J$1, "0", (DAYS($R$6,A6)*$J$2)+$J$2), A6>=$R$6, IF($S$6>=$J$1, "0", (DAYS($R$7,A6)*$E$1)+$J$2), A6>=$R$7, IF($S$7>=$J$1, "0", (DAYS($R$8,A6)*$E$1)+$J$2)), but the values keep returning as 0.
