nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

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.

Find more posts tagged with

Accepted answers

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))

All comments

David Tutwiler

Formula

Stitched

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

David Tutwiler

Awesome! Glad to hear it is working.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.