Count the number of times a specific column changes!

Hi Everyone!!

I’m Boaz, a Senior Customer Success Manager in the Enterprise team at monday.com. I work with large Enterprises and assist them with getting the most out of monday.com.

TL;DR:

I’m sharing a cool hack that allows you to use the combination of 2 automations, a date column and a special formula, to automatically keep track of the number of times a due date, or any column that you choose, is changed.

What we are doing here is “tricking” the system using this combination of features to achieve something we want.

Let’s say that you are managing a content team who create posts for social media. You set a due date for the post and then, as we have all experienced, the due date is pushed multiple times.

It’s a good learning experience to keep track of the number of times the date (or any other column) is changed, so you can prepare for next time. There is no automatic way to count the number of times the value in a particular column is changed. You would need to manually count this from the activity log. Well, with this hack, we can count this automatically! Let’s see how to achieve that?

I’ve made this short video which runs through the workflow in action and explains how to set it up.

In this example we are tracking the number of times a date column changed, but this can also be applied for any other columns!

How to implement:

After watching the video, here are the key steps to implement the workflow on a board where you want to track the changes:

Add the required columns

  • Add the column you want to keep track on the number of times it changes.

In our example we will use a date column, but you can use any column you want.

  • Add the ‘Creation log’ column- we will use this column in the formula later on.

This will be used so we can always see when this item was created.

  • Add another ‘Date’ column

We can call it ''Date change"- we will use this column in the formula later on.

  • Add a ‘Formula’ column,

Let’s name it ''Change counter Formula".

Now lets apply some automations:

  • The first automation sets the ''date change" column to the item’s creation date.

  • The second automation pushes the “date change” by 1 each time the ‘due date’ column is changed.

Set up the following formula in the ''Change counter Formula" column:

  • IF(DAYS({(Date change)},{Creation Log#Date})=0,0,DAYS({(Date change)},{Creation Log#Date})-1)

  • What this formula does, in words, is:

  1. Check the number of days between the ‘Date change’ column and the ‘Creation log’ column.

  2. If the difference is 0 —> return the value 0 (=if its the same date show 0)

  3. If the difference is not 0 —> calculate the difference and subtract 1 from the result (we need to subtract 1 because the 1 time we set the due date also counts as a date change and triggers the 2nd automation, but we don’t want to count that initial date setting as a ‘change’).

Enjoy and let me know if you implement the workflow how it has helped you!

14 Likes

Hi,

Very good work, and pretty simple too! All native and useable for future triggers & automations. Good job :wink:

2 Likes

Could this work using a timeline column instead of a date column??

This can work with any column you can use in the 1st automation mentioned in the description.

1 Like

@BoazArad, this is amazing!!! We have been trying to find a way to count the number of times a status changes. Thank you for sharing this hack!

1 Like

This is great! Thank you so much for sharing. One of my managers has been requesting a count for how many times an item has been changed to a specific status and with these two automations and the formula, I’m not able to provide this information for him.

Hi @Kristinemd13
You can achieve just that with the above formula. Instead of the 1st automation you will need to use something like this:
image
Hope this helps!

1 Like

Hi! I can’t seem to create either of these automations to make my columns work. Can you help me find and or create them? Thanks!

1 Like

I couldn’t fin them either but I found something else which looks simpler and I think achieves the same result. When Column Changes then increase Number by X.

Perhaps the automations posted above are already obsolete and monday.com has found a simpler way to do this? This does happen quite fast in the platform as it becomes better and better every year.

image