If function based on status column, calculate days

Platform discussions Special workflows & use cases
1

I am trying to display whether each task is ahead of schedule, on-time or behind schedule. The goal is to have a column automatically determine if this Task was completed early, on-time, or late. If the Task’s column “Completion Date” cell has a date (meaning it is done), then the formula subtracts the Completion Date from the Goal Due Date. If there is no date yet entered in the “Completion Date” cell, then the formula subtracts the Completion Date from Today’s Date.

A negative number means the Task was completed ahead of schedule. A positive number means the Task was completed behind schedule.

My proposed formula is:

IF “Completion Date” has a date, then Days({Goal Due Date}, {Completion Date} else Days{Goal Due Date}, {Today’s Date})

Does anyone know how to write this so that it would work in Monday.com? If we could have the “Completion Date” auto-populate the date that the status was changed to “Done” that would be even better. So if anyone knows how to do this as well, I would appreciate it. Thanks

2

Hi @RobLo,

Try this:

IF({Completion Date}="",
      DAYS({Goal Due Date},TODAY()),
      DAYS({Goal Due Date},{Completion Date})
)

Hope it helps.

 

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.

1 Like
3

This worked perfectly, thank you!