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

Platform discussions monday apps & integrations
, ,
1

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
2

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.

3 Likes
3

Nice! I’ll implement and report back!

4

@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
5

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

6

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*

7

Thank you both! Trying them now!

1 Like
8

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

9

Do you have any ideas on how to track the amount of days passed betwee creation date and status = done date?

10

Hi Terry,

You would need to add a new date column and have an automation that sets it to the current date when Status changes to Done.

2025-01-23_10-03-26
2025-01-23_10-03-26608×325 18.2 KB

You may want to add other automations that clear the date when the status is set to another value than done, in case someone reverts the change (would require as one automation per statuses other than done).

Now that you have 2 date columns, you can calculate the number of days between the 2 with a formula column which syntax is:

DAYS({Date},{Creation log})

 

Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.

  • Create formulas without using the Formula column (and avoid its limitations)
  • Build formulas involving data from the previous item, the next item, the sub-items, the parent item, even items in the same group or the same board.
  • In one formula, update multiple columns from multiple items.

Check our blog for real use cases.