Calculating fines due based on calendar date

Stitched

Hi,

I am looking for help with calculating fines based on a missed due date, and the subsequent number of calendar days past the due date. Ideally, I would like the fine to calculate in two instances. 1) if the date in "Y1Q1 Date Payment RCVD" is greater than the due date, OR 2). the date is blank and today's date is greater than the due date.

For example:

-if the fine was $1 per day, and the date the payment was received was 1/20/24, the fine would be $5.

-if today's date was 1/20/24 and the payment date had not been recorded, it would be $5 and tomorrow it would be $6, and so on until a date is recorded.

-if there's no date recorded, but the due date has not been reached, the field would remain blank.

David Tutwiler

I think this will work. The first part of the formula handles if a payment was received but was past the due date, and the second part handles if TODAY() is past the due date but no payment shows as received. It should return blank if neither requirements are met, so it inherently solves the third example.

Formula

=IF(NOT(ISBLANK([Y1Q1 Date Payment RCVD]@row)), IF([Y1Q1 Date Payment RCVD]@row > [Due Date]@row, [Y1Q1 Date Payment RCVD]@row - [Due Date]@row), IF(TODAY() > [Due Date]@row, TODAY() - [Due Date]@row))

David Tutwiler

I think this will work. The first part of the formula handles if a payment was received but was past the due date, and the second part handles if TODAY() is past the due date but no payment shows as received. It should return blank if neither requirements are met, so it inherently solves the third example.

Formula

=IF(NOT(ISBLANK([Y1Q1 Date Payment RCVD]@row)), IF([Y1Q1 Date Payment RCVD]@row > [Due Date]@row, [Y1Q1 Date Payment RCVD]@row - [Due Date]@row), IF(TODAY() > [Due Date]@row, TODAY() - [Due Date]@row))

Stitched

David! My hero! Thank you so much! This worked perfectly

David Tutwiler

Awesome! Glad to hear it is working.