Help Needed: Calculating On-Time/Late Payments and Automating Annual Summary

Ronald Anderson

Hello Smartsheet Community,

I hope this message finds you well! I'm currently looking for some assistance with optimizing my payment tracking sheets.

1. Calculating On-Time or Late Payments: I'm trying to establish a reliable method to determine whether monthly payments are made on time or are late. What is the best approach to achieve this within Smartsheet? Are there specific formulas or features that you recommend for tracking payment status effectively?
2. Collecting Payment Dates for Annual Summary: Additionally, I want to create an annual summary sheet that automatically compiles payment dates without needing to edit formulas every year. Is there a way to set this up so that as I update the monthly payment data, it reflects in the annual summary without manual adjustments each year?

Any insights or examples would be greatly appreciated. Thank you for your help!

KPH

Hi @Ronald Anderson

It looks like your January 2024 column contains dates, and you will have a column for each month with dates in. If you are able to reserve the top row of your sheet and do not need to use column formulas, then you could put the start date of the month in that row and use that in your formula.

Something like this: (note I am using US date format)

The formula in column 6 is:

=IF(ISDATE([January 2024]@row), IF([January 2024]@row <= [January 2024]$1 + 9, "On-Time", IF([January 2024]@row > [January 2024]$1 + 9, "Late", "Not Submitted")), "Not Submitted")

The changes I made to yours are shown in bold.

1. Remove the fixed date and instead referenced the date in the sheet plus 9 days. (you could of course put the 10th as the date in the sheet and not add 9 days)
2. As 1, but also removed the = as any payments on the date will already be reported as "On-Time" as the met the first conditaion.

Dragging that to column 7 automatically updates the references to this:

=IF(ISDATE([February 2024]@row), IF([February 2024]@row <= [February 2024]$1 + 9, "On-Time", IF([February 2024]@row > [February 2024]$1 + 9, "Late", "Not Submitted")), "Not Submitted")

You don't need to change anything in this formula, just drag it to the column to the right.

You could also use this formula in the first row to update the dates for you, so you don't need to enter those.

=IF(MONTH([January 2024]@row) < 12, DATE(YEAR([January 2024]@row), MONTH([January 2024]@row) + 1, 1), DATE(YEAR([January 2024]@row) + 1, 1, 1))

And drag that across to other columns to automatically fill in the date.

If this isn't how your data is structured, hopefully it will inspire an approach that does work.

Ronald Anderson

Ok, I understand now. I was missing the first date.

Thanks, again.

Ronald Anderson

Here is my current formula that determines submission status:

=IF(ISDATE([January 2024]@row), IF([January 2024]@row <= DATE(2024, 2, 10), "On-Time", IF([January 2024]@row >= DATE(2024, 2, 10), "Late", "Not Submitted")), "Not Submitted")

Using this formula, I have to update the year for all 12 months every year.

Ross Loomis

you could try to replace the "2024" with YEAR(TODAY()). that would always use the current Year.

KPH

Hi @Ronald Anderson

It looks like your January 2024 column contains dates, and you will have a column for each month with dates in. If you are able to reserve the top row of your sheet and do not need to use column formulas, then you could put the start date of the month in that row and use that in your formula.

Something like this: (note I am using US date format)

The formula in column 6 is:

=IF(ISDATE([January 2024]@row), IF([January 2024]@row <= [January 2024]$1 + 9, "On-Time", IF([January 2024]@row > [January 2024]$1 + 9, "Late", "Not Submitted")), "Not Submitted")

The changes I made to yours are shown in bold.

1. Remove the fixed date and instead referenced the date in the sheet plus 9 days. (you could of course put the 10th as the date in the sheet and not add 9 days)
2. As 1, but also removed the = as any payments on the date will already be reported as "On-Time" as the met the first conditaion.

Dragging that to column 7 automatically updates the references to this:

=IF(ISDATE([February 2024]@row), IF([February 2024]@row <= [February 2024]$1 + 9, "On-Time", IF([February 2024]@row > [February 2024]$1 + 9, "Late", "Not Submitted")), "Not Submitted")

You don't need to change anything in this formula, just drag it to the column to the right.

You could also use this formula in the first row to update the dates for you, so you don't need to enter those.

=IF(MONTH([January 2024]@row) < 12, DATE(YEAR([January 2024]@row), MONTH([January 2024]@row) + 1, 1), DATE(YEAR([January 2024]@row) + 1, 1, 1))

And drag that across to other columns to automatically fill in the date.

If this isn't how your data is structured, hopefully it will inspire an approach that does work.

Ronald Anderson

Thank you KPH! This is a better way of doing it.

Ronald Anderson

@KPH The formula you provided to put in the monthly dates is returning a circular reference error.

=IF(MONTH([January 2024]@row) < 12, DATE(YEAR([January 2024]@row), MONTH([January 2024]@row) + 1, 1), DATE(YEAR([January 2024]@row) + 1, 1, 1))

KPH

Are you putting it in the January 2024 column? You need to put one date in, the 1st Jan 2024 in the January 2024 column, then this formula in the adjacent column. It will return the date one month after that in the January 2024 column. Then you drag that formula to the right and each adjacent cell will return the next month.

Ronald Anderson

Ok, I understand now. I was missing the first date.

Thanks, again.

KPH

Excellent, I'm glad to have helped!