Stuck on a cross-sheet formula for overdue items

Hi, I need to create a stacked bar chart with how many items are due a month ( I have gotten that formula) by doing =(COUNTIFS({ Range 1}, >=DATE(2022, 1, 1), {Range 1}, <=DATE(2022, 1, 31)) + the preceding 5 sheets. I also have added how many are completed on time, how many are not.

However I also need to do how many are carried over to the next month. I had planned on adding a drop down column to all the sheets saying (yes/no) if carried over to the next month , but that will not work as some carried over to two months ect.

Any help is greatly appreciated! Thank you

Answers

  • sharkasits
    sharkasits ✭✭✭✭✭

    @Molly12345 Can you provide a little more detail? How is the data currently structured, and what do you want the final stacked bar chart to look like?

  • Molly12345
    Molly12345 ✭✭
    edited 02/14/23

    Hi, thank you so much for replying and apologies for the delayed response.

    Data is structured in the sheets as follows

    Rarely is a task completed by a due date. it is usually within the tolerance end ( never early at the tolerance start). Is it possible to make a formula to show the due date has then been carried over to the next month? Or to do it i in my chart metrics sheet. Occasionally it can be carried over a 2nd month depending on the tolerance window.


    I have a bar chart made already and wanted to add , do a due date carried over. But I do not know if this is possible.

    Many thanks in advance 😁

  • Julie Fortney
    Julie Fortney Overachievers

    Hi @Molly12345, if a task is carried over into the next month, once it's completed, does it count as late? I'm trying to envision what the carried over due date signifies.

    Let's say that you make Carried Over Tasks yellow in the bar chart. If I see one yellow part on the bar in July 2023, does that mean that there was a task originally assigned in a previous month that was then carried over to and completed in July?

    www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!