Formula to produce days worked on - days in pending, and days to completion date

Hello! I’m hoping someone can help me out with this formula I’m trying to create.

I have two columns that are labeled Date Opened and Complete Date. I’m using this formula to produce the number of days it’s been getting worked on.
IF(DAYS(TODAY(), {Date Opened}) <= 30, “0-30 days”,

IF(DAYS(TODAY(), {Date Opened}) <= 60, “31-60 days”,

IF(DAYS(TODAY(), {Date Opened}) <= 90, “61-90 days”,

IF(DAYS(TODAY(), {Date Opened}) <= 120, “91-120 days”,

IF(DAYS(TODAY(), {Date Opened}) <= 150, “121-150 days”,

IF(DAYS(TODAY(), {Date Opened}) <= 180, “151-180 days”,

IF(DAYS(TODAY(), {Date Opened}) <= 210, “181-210 days”, “210+ days”)))))))

This formula, however, does not recognize the completed items. It will assume that the completed items are still being worked on. How can I make the formula recognize that if it is complete it needs to calculate between date opened and complete date. Or how do I make the formula recognize that if there is a complete date, it needs to calculate between those two dates.

Thank you!

1 Like

Use the following formula. But use straight quotation marks.

IF(OR(DAYS(TODAY(), {Date Opened}) <= 30,DAYS({Complete Date}, {Date Opened}) <= 30), “0-30 days”,
IF(OR(DAYS(TODAY(), {Date Opened}) <= 60,DAYS({Complete Date}, {Date Opened}) <= 60), “31-60 days”,
IF(OR(DAYS(TODAY(), {Date Opened}) <= 90,DAYS({Complete Date}, {Date Opened}) <= 90), “61-90 days”,
IF(OR(DAYS(TODAY(), {Date Opened}) <= 120,DAYS({Complete Date}, {Date Opened}) <= 120), “91-120 days”,
IF(OR(DAYS(TODAY(), {Date Opened}) <= 150,DAYS({Complete Date}, {Date Opened}) <= 150), “121-150 days”,
IF(OR(DAYS(TODAY(), {Date Opened}) <= 180,DAYS({Complete Date}, {Date Opened}) <= 180), “151-180 days”,
IF(OR(DAYS(TODAY(), {Date Opened}) <= 210,DAYS({Complete Date}, {Date Opened}) <= 210), “181-210 days”,
“210+ days”)))))))

Hi @erika_gomez,

You actually don’t need any if statement, except if you really want to group all dates above 210 days into a single category.

The following will return: 1-30 days, 31-60 days,…, 211-240 days, 241-270 days, etc.

CONCATENATE(
    1 + FLOOR(DAYS(TODAY(), {Date Opened}),30),
    "-",
   CEILING(DAYS(TODAY(), {Date Opened}),30),
   " days"
)

If you really want to group all above 210 days into a single category, then use this syntax:

IF(DAYS(TODAY(),{Date Opened})>210,
  "210+ days",
  CONCATENATE(
     1+FLOOR(DAYS(TODAY(),{Date Opened}),30),
     "-",
     CEILING(DAYS(TODAY(),{Date Opened}),30),
     " days"
   )
 )

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.

Hello! Thank you for taking the time, the formula still shows up as “Illegal Formula”

Do the column titles match yours exactly? Are you using straight quotation marks?

Good Morning!

The column titles match mine exactly. I copied and pasted directly from your message as well.

My message had directional quotation marks. They need to be unidirectional. Try this one:

IF(OR(DAYS(TODAY(), {Date Opened}) <= 30,DAYS({Complete Date}, {Date Opened}) <= 30), "0-30 days",
IF(OR(DAYS(TODAY(), {Date Opened}) <= 60,DAYS({Complete Date}, {Date Opened}) <= 60), "31-60 days",
IF(OR(DAYS(TODAY(), {Date Opened}) <= 90,DAYS({Complete Date}, {Date Opened}) <= 90), "61-90 days",
IF(OR(DAYS(TODAY(), {Date Opened}) <= 120,DAYS({Complete Date}, {Date Opened}) <= 120), "91-120 days",
IF(OR(DAYS(TODAY(), {Date Opened}) <= 150,DAYS({Complete Date}, {Date Opened}) <= 150), "121-150 days",
IF(OR(DAYS(TODAY(), {Date Opened}) <= 180,DAYS({Complete Date}, {Date Opened}) <= 180), "151-180 days",
IF(OR(DAYS(TODAY(), {Date Opened}) <= 210,DAYS({Complete Date}, {Date Opened}) <= 210), "181-210 days",
"210+ days")))))))

This worked perfectly! Thank you so much!

1 Like