Trying to change a cell value when a date is reached

jmaxbr

I'm trying to change an invoice status to "Overdue" when the date is > 20 days later than the date in the Invoice Date column. I can't seem to find the automation conditions!

Laurie Olson

I set up a little test sheet to show you how.

You can hide the test flag. I prefer to use flags for these kinds of checks, as it runs faster.

Here is the test for late formula:

=IF(TODAY() - [Invoice Date]@row > 20, true)

Here is the formula for Status:

=IF([Test for Late]@row = true, "Overdue", "Coming Due")

Laurie Olson

Yes, all is dependent on someone entering the date the invoice was paid.

jmaxbr

Thanks, this was very helpful and I've got everything I need now!

jmaxbr

Alternatively, I set up an automation to change the status to "Overdue" when the Health column changes to "Red", which I was first able to do based on the invoice date, so now it works.

I'm still interested in the solution to the original question if anyone has one?

Laurie Olson

I set up a little test sheet to show you how.

You can hide the test flag. I prefer to use flags for these kinds of checks, as it runs faster.

Here is the test for late formula:

=IF(TODAY() - [Invoice Date]@row > 20, true)

Here is the formula for Status:

=IF([Test for Late]@row = true, "Overdue", "Coming Due")

Laurie Olson

Here is the result when late:

Laurie Olson

I wouldn't do this in automations, as they take a couple minutes to run, and it is confusing to the user when there is a delay. I like to put these things right in the columns. Both column formulas, test for late, and status, would be a column formula, so I would lock them. You may have other values to add to the status column, and you just string the if statements with commas between.

Laurie Olson

One thing I am missing here is how do we know when the invoice is paid, as otherwise, all will become late without some other indicator. I would add a paid date column. So, adding a column for date paid:

The Test for Late formula would look like this:

=IF(AND([Date Paid]@row <> "", [Date Paid]@row - [Invoice Date]@row > 20), true, IF(AND(TODAY() - [Invoice Date]@row > 20, [Date Paid]@row = ""), true))

We just added another scenario where we test to see if it was paid, and if so, we test to see if there are more than 20 days between the invoice and paid dates.

The Status formula would change to reflect paid status. I added these values to Status:

The Status formula looks like this:

=IF(AND([Date Paid]@row <> "", [Test for Late]@row = true), "Paid Late", IF(AND([Date Paid]@row <> "", [Test for Late]@row = false), "Paid On Time", IF(AND([Date Paid]@row = "", [Test for Late]@row = true), "Overdue", "Coming Due")))

The first IF statement checks to see if date paid is populated and test for late to see if true, then indicate paid late.

The second IF statement is the same, but if the flag is not true, then we are not late and it was paid on time.

The third IF statement is for those where date paid is blank and it is late, indicated overdue

For everything else, it is coming due.

The following reflects an invoice that is not late, and was paid:

Here is an invoice paid late:

jmaxbr

Great point about the date paid column - I don't personally handle the payments myself so this is a good plan, otherwise it wouldn't be an issue ;)

I'm not sure I follow your "test for late" idea however - in my scenario, the invoices are basically due when issued, we don't really have payments terms. So it is a bit of a "how long has this been outstanding or unpaid" thing.

Laurie Olson

You can use whatever terminology you like to describe it in the system. Call it Test for 20 days late.

jmaxbr

How do you use the "test for late" in practice? Whomever pays the invoice just enters the date they pay it, and that triggers the flag and the status change?

Laurie Olson

Yes, all is dependent on someone entering the date the invoice was paid.

jmaxbr

Thanks, this was very helpful and I've got everything I need now!