Formula to count only if status column changed today

Platform discussions
, ,
1

Hello all.

I’m working on creating a chart that measures progression on tasks for the day. So the goal is to create a formula that will count anytime a task or subitem changes status ( to Done or Stuck only) AND the last updated column shows today.

The main issue is with counting the last updated column when it shows today’s date. I can’t seem to make the formula work. If anyone has any advice, or a formula to accomplish both I’d be very grateful.

2

Hey Sidney!

Charlotte here with the monday Community team! :wave:

Just to clarify, can you expand a little more on what you want the formula column to show when the status is “Done” or “Stuck” and the Last Updated column was updated today? Are you wanting it to say something like “Overdue” or “Not done” or something else?

Also, can you send over a screenshot of the formula you’re currently working with so we can see what you have so far and how we can reformat it to show what you need?

Thanks, Sidney! Looking forward to hearing from you!

4

And just to clarify. This formula doesn’t work because the [Last Updated] = Today () is not correct.

5

image

6

Hi Sidney,

If you want to compare the date part of 2 dates (without the time), you’ll need to use FORMAT_DATE as in

FORMAT_DATE({Last Updated},"YYYYMMDD")

So, you formula would become:

IF(AND({Status}="Done",FORMAT_DATE({Last Updated},"YYYYMMDD")=FORMAT_DATE(TODAY(),"YYYYMMDD")),1,0)

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

1 Like
7

That worked perfectly. Thank you Gilles.

9

Update: The current formula works but I’ve run into a new issue. I’m getting double counts for sub-items completed that also happen to complete the status at the parent level. These situations should only count as 1 instead of 2.

Is there a way to have the formula not trigger if there are sub-items for the parent task?

Current formula for parent and sub-items:
IF(AND(Or({Status}=“Stuck”,{Status}=“Done”),FORMAT_DATE({Last Updated},“YYYYMMDD”)=FORMAT_DATE(TODAY(),“YYYYMMDD”)),1,0)