Formula for "Overdue" or "On Time" based on 'Status' and 'Due Date'

Platform discussions
1

Hi,

Pretty simple request, but struggling to find guidance.

I want to create a formula which gives me a value of either Overdue or On Time based on an Overall status column and due date.

The logic would be as follows:

If OVERALL STATUS is not DONE and DUE DATE isin the past generate value OVERDUE

2

This should work. Obviously update the dynamic values according to your column names.

IF(
    AND(
        NOT({Overall Status}="Done"),
        FORMAT_DATE(
            TODAY(),
            "YYYY-MM-DD"
        )>
        FORMAT_DATE(
            {Due Date},
            "YYYY-MM-DD"
        )
    ),
    "Overdue",
    "On Time"
)
3

Maybe this isn’t what you’re after, but Monday does have a built-in feature for deadlines that shows when items are overdue. See this article for more info.

Some example images:
image

image
image1493×296 12.6 KB

1 Like
4

Thanks Francis, unfortunately I can’t get this to work. Perhaps that’s just me, but just get errors when trying to run

5

Calum’s suggestion is also great!

Feel free to share a screenshot of the formula and your board and I’d be happy to take a look. You’ve most likely made a syntax error when implementing the formula.

1 Like
6

Hiya, I use a timeline column, date column plus some formula columns.

The date column has an automation to set the completed date to the day the status changes to done.

The first formula column shows if the task was completed early or late.
IF({Completed Date}=“”,“N/A”,DAYS({Timeline#End},{Completed Date}))

The second formula column tells me if the task is pending/overdue or if it was completed on time or late.
IF({Timeline}=“”,“Pending”,If({Status}=“Done”,If({Completed Early/Late}<0,“Done Overdue”,“Done On Time”),If(DAYS(TODAY(),{Timeline#End})<0,“Pending”,If(rounddown(DAYS(TODAY(),{Timeline#End}),0)=0,“Due Today”,“Overdue”))))

I’m pretty sure I got all this from a help article but can’t find it now sorry!

7

Hi everyone! :wave:

I suggest you to try Time in Status app which track time the Item has spent in each workflow status. So you can monitor how fast the Item moves between statuses.
You’ll get user-friendly data on the board in a few seconds.

Знімок екрана 2024-01-26 о 03.33.48
Знімок екрана 2024-01-26 о 03.33.481920×488 50.2 KB

Also you will be able to track how much time your issue spends in each status and much more by generating 3 types of reports:

:chart_with_upwards_trend: And use charts to visualize your reports a sample of a certain number according to time range/item range, or a single item:

  • Pie Charts
  • Bar Charts
  • Area Charts

The app is also available in the format of board integration.

Знімок екрана 2024-01-26 о 03.35.01
Знімок екрана 2024-01-26 о 03.35.011538×634 95.8 KB

You can try it on the Monday Marketplace and share your experience with us.
Hope you find this app helpful.

8

Hello Calum,

Those 2 reports are awesome, exactly what I’m looking for.
How did you succeed to generate it?

9

Hey Sorin,
Happy that my sample caught your eye. :slight_smile:

For the first chart, we have it showing the sum of overdue items in the Y axis, and on the X axis there’s two things going on:

  1. Items are grouped by the month of a Date column.
  2. Items within each group are separated (stacked) by a Status column.

Steps to create the chart:

  1. Create the following columns:
    a. People
    b. Status
    c. Date
  2. From the 3-dot menu on your Date column choose Settings, and then Set as a deadling:
    image
  3. Select the People and Status columns you want to associate with the deadline on the next screen.
  4. Create a Blank View or a Dashboard
  5. Choose + Add Widget and select the option for Chart.
  6. From the 3-dot menu of the Chart, choose Settings.
  7. For Chart Type, choose Stacked Bar Chart.
  8. For X Axis, choose the Date column from step 1, and then select the grouping you prefer.
  9. For Stack By, choose the Status column from step 1, and then be sure to check the box for Expanded Stack Mode if you want the chart to separate the statuses like I did in the sample.
  10. For Y Axis, choose Count items and Sum.
  11. Finally filter the Chart to show only items whose Date column Is Overdue/Done on time/Done Overdue like this:
    image
    image1480×410 45.1 KB

For the Overdue Items by People chart, we only have to change the Stack By option to the People column from step 1.

Let me know if you were able to find this helpful. Best of luck to you @sorincb! :+1:

10

Thanks a lot man <3

1 Like