How to do a count up of days based on when a status changes?

We have an integration with Salesforce where the status of an item in Salesforce pulls over to one of our boards. We want to create a column (with a formula I’m assuming) around when the status column changes to a certain value (for example from “Not Started” to “In Progress”) it triggers a number field to start counting up the days since that status changed.

For example, if today (1/30/24) the status column changes to “In Progress”, tomorrow on 1/31, that day count value would be 1. So we can track how long it’s been since that status changed to “In Progress”

1 Like

Hi Karen,

First I would add a date column called “Date Changed” and create an automation that says:
"When Status changes to “In Progress”, set “Date Changed” to today.

Then I would add a formula column which calculates the number of days between Date Changed and today.

DAYS({Changed Date},TODAY())

Looking for a simpler way to write complex formulas? Check out the Advanced Formula Booster at https://mdboosters.com. It’s a convenient third-party app that simplifies formula writing. With it, you can spread your formulas across several lines, utilize variables, and access dozens of functions not found in the standard formula column.

2 Likes

Nice! I’ll implement and report back!

@GCavin - the formula worked! But it’s giving us a number with 3 decimals. Would you be able to help with how we could round the number to a whole number?

image

1 Like

Hello Karen! you can solve it with the following formula:

ROUNDDOWN(DAYS(TODAY(),{Changed Date}),0)

Captura de Pantalla 2024-02-08 a la(s) 14.05.21

Hi Karen,

If you want a positive number, reverse the date positions:

DAYS(TODAY(),{Changed Date})

To round use, ROUND, ROUNDUP or ROUNDDOWN

ROUND(DAYS(TODAY(),{Changed Date}),0)
ROUNDUP(TODAY(),{Changed Date}),0)
ROUNDDOWN(TODAY(),{Changed Date}),0)

ROUND rounds to the nearest value (down under .5, up for .5+)
ROUNDUP always rounds up.
ROUNDDOWN always down.
The 0 at the end is the number of decimals.


Want to get rid of the formula column? and write formulas that support multiple lines, variables and can update various columns at once? Take a look at the Advanced Formula Booster at https://mdboosters.com*

Thank you both! Trying them now!

1 Like

Great! You’re welcome.
Let me know if it worked for you!