Calculating Business Days Lead Time

[FrancisElliott]

The Problem

I recently had a client who wanted to look at lead time for tasks to be completed in their organization. They weren’t happy with any of the default options, which tend to give poor results.

1. WORKDAYS() is too imprecise by itself. If a task is started at 4pm on a Friday and finished at 10am on the following Monday, this gives a value of 2. A task started at 9am on a Monday and finished at 10pm the next day would give the same result. What we want is for the former to reflect how little actual business time there was between them.
2. DAYS() gives us some information about the time, however it doesn’t account for working days. Additionally, it fails to capture the concept of working hours. If we start something at 10am and finish at 2pm, that will give us a result of 0.166666 days, which isn’t really helpful.
3. Time Tracking Column fails for similar reasons to DAYS()—it doesn’t account for working vs non-working hours. We can create automations to turn it off and on at certain times. This is a little complex though, and it eats into our automation count a lot if we want to run this for loads of different tasks.

The Solution

I created a formula that accounts for all of the above.

The client was already generating a Ready and Done time for tasks using simple monday.com automations to populate Date columns based on Status changes.

The formula then returns a number of working days, with the decimal representing a work day, rather than a full day. For instance, if we have a Ready Time of 2pm on Tuesday and a Done Time of 6pm on Thursday, we get a result of 2.5. This is an approximation of the “Business Days Lead Time”. i.e. it’s the number of working days it took for something to get done, accounting for hours.

Here are some example results (I used formula columns to show the days of the week here—my formula works with regular date columns that have times added).

Comparison with DAYS() and WORKDAYS()

Here is the formula:

SUM(
    MINUS(
        WORKDAYS(
            {Done Time},
            {Ready Time}
        ),
        1
    ),
    MIN(
        MAX(
            MULTIPLY(
                MINUS(
                    DAYS({Done Time},{Ready Time}),
                    DAYS(FORMAT_DATE({Done Time},"YYYY-MM-DD"),FORMAT_DATE({Ready Time},"YYYY-MM-DD"))),
                3
            ),
            -1
        ),
        1
    )
)

How it works:

1. We get the WORKDAYS() for the two dates and subtract 1. This gives us a baseline that we can combine with an offset—this offset will account for differences in hours between the Ready and Done Times.
2. We find an offset. We do this by comparing the DAYS() result (which accounts for hours) with another DAYS() result that removes the hours. This gives us either a positive or negative number (if the Start Time is later than the Done Time, we’ll get a negative result here).
3. We then need to modify the offset to give us a proportion of a business day. We do this by multiplying the result by 3. Each hour of difference is now worth 1/8 of a day. We can modify this if we want to assume a shorter or longer work day. NOTE: that the formula doesn’t check when the tasks were Ready or Done within the day—it just checks the difference between them. This is broadly helpful, especially when dealing with organizations that work across time differences.
4. We also need to clamp the offset between -1 and 1, which we do with MIN() and MAX(). This prevents us from adding more than one business day if a task was finished while working overime, for instance.
5. Finally we add the offset to the modified WORKDAYS() result.

Potential Developments

1. Filter out incomplete data.
2. Have a rolling calculation by using a current time until a task is actually done.
3. Include this formula conditionally for Internal/External activity.
4. Have this formula account for different working day lengths for different people.
5. Calculate this value as business hours rather than days.
6. Account for national holidays

Possible Use Cases

1. Chart the average business days from when a task is given to when a task is completed by Assignee, Department, Type, Month, Year etc.
2. Use above calculation for accurate projections about when a task/project can be delivered for a customer.
3. Create views that highlight tasks which took longer than X business days to complete with less confusion over “oh that one was assigned on a Friday afternoon, so don’t worry”
4. Track external requests. How many business days does it take to get a response or action from X agency? Factor that into project planning. This could also be used to calculate a real average business days shipping time—hold your couriers to account!

---

Hopefully this is useful! There are definitely some limitations to this, but in general it provides a clearer picture of business lead time compared with the default offerings.

I’d love to hear any feedback or suggestions—or just if you found use for this in your solution.

[BiancaT (Bianca Taylor)]

Incredible! Thank you so much for sharing Francis, this will help our community immensely!

[mark.anley (Green Llamas Consulting Inc.)]

Brilliant Solution @FrancisElliott!