Formula for Overdue using timeline column

Need help with a formula please. This formula IF(MULTIPLY(DAYS({DUE DATE},TODAY()),-1)>0, “Overdue”, “On Time”)​​ is returning and “Illegal Formula” error when applying it to a timeline #end column. here is the formula: IF(MULTIPLY(DAYS({DUE DATE},TODAY()),-1)>0, “Overdue”, “On Time”)​​. (i want to be able to display a count of past due items on a dashboard)

Thanks in advance!

Hi @amadams1216 sounds like there might be an easier way to do this; with status automation instead of formula.
You could use the automation “When date arrives and status is not complete change to overdue” And then you could report on that status column in a dashboard.

Is there a reason you’re wanting to use the formula column instead?

1 Like

other than limited automations, a formula can calculate in real-time based on the variables that may move up and down vs in a consistent linear fashion. (ex: status = not started, end date is in the past. status changes to working on it, and end date is changed to future date. then status changes back to not started). covering all the different combinations of what can change and when seems to be more complex than simply calculating whether an item i late when it is not done and end date is in the past.

thanks for asking.

1 Like

@amadams1216,

I’m guessing that the primary issue is your quote marks. It’s difficult to say because of the way the community group post editor works. Specifically, if you include quotes here without designating the text as “preformatted”, it will change normal quotes to “smart quotes” (where beginning quote marks are different than end quote marks.) To fix this, edit the formula and delete the existing quote marks and then rekey them. If this is the problem your formula will no longer give the error.

However, based on your description of what you are trying to accomplish, I think you you are referencing the timeline column incorrectly. As it is, the formula will be using the start date of the timeline value. If you do, in fact, want to refer to the END DATE in the timeline then the formula text should read something like this:

IF(MULTIPLY(DAYS({DUE DATE#End},TODAY()),-1)>0, "Overdue", "On Time")

thank you for the response. Unfortunately, i am still getting the same error. here is the actual formula i am using (modified from the Monday.com forumla list)
IF(MULTIPLY(DAYS({Start - Complete#End},TODAY()),-1)>0, "Overdue", "On Time")​​
(thanks for the tip on preformatted text)

@amadams1216,

I have no explanation as to why… BUT you have some strange hidden characters in the text…

Edit the formula, put you cursor between the last quote and the right paren, hit delete 5 times, rekey the paren, save the formula.

1 Like

oh, my goodness! that worked. (how the heck did you figure that out?)

Thank you so much!

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.