Count Days and Durations

I am looking to count days used in my schedule per task. I am set up with start and finish date columns along with a duration column. I am trying to calculate days on site from start date to today, but to stop counting days when finish date is reached.


Best Answer

  • Matthew J McAteer
    Matthew J McAteer ✭✭✭✭
    Answer ✓

    Hi @Caleb W

    Sure, try this: =IFERROR(IF(AND([Start Date]@row <= TODAY(), [End Date]@row > TODAY()), NETWORKDAYS([Start Date]@row, TODAY()), IF(AND([Start Date]@row <= TODAY(), [End Date]@row <= TODAY()), NETWORKDAYS([Start Date]@row, [End Date]@row), "0")), "")

    Once you add the formula to a cell, you can right click and convert the formula to a column formula.

    Hope that helps 😊

Answers

  • HI @Caleb W

    Try this: =IF([End Date]@row < TODAY(), Duration@row, IF([End Date]@row > TODAY(), NETWORKDAYS([Start Date]@row, TODAY())))

    Matthew

  • Caleb W
    Caleb W ✭✭✭✭

    Thank you @Matthew J McAteer. Can we adjust that so I can make it a column formula, but will not start tracking until start date is = to today?

  • Matthew J McAteer
    Matthew J McAteer ✭✭✭✭
    Answer ✓

    Hi @Caleb W

    Sure, try this: =IFERROR(IF(AND([Start Date]@row <= TODAY(), [End Date]@row > TODAY()), NETWORKDAYS([Start Date]@row, TODAY()), IF(AND([Start Date]@row <= TODAY(), [End Date]@row <= TODAY()), NETWORKDAYS([Start Date]@row, [End Date]@row), "0")), "")

    Once you add the formula to a cell, you can right click and convert the formula to a column formula.

    Hope that helps 😊

  • Caleb W
    Caleb W ✭✭✭✭

    @Matthew J McAteer Thank you for the help on the formula. That did exactly what I was looking for.

  • Caleb W
    Caleb W ✭✭✭✭

    @Matthew J McAteer , I am using a formula to sum the days that each of our Foreman install certain types of product. Right now I am set up with a column that I enter the type in, but I would like to join that into a scope column so I am not double entering data. Is there a way to take the formula I am using and ask it to look at "if contains" the type? In the example below, "Precast Install" would be my type.

    =SUMIFS({Days on Site}, {Scope}, "Precast Install", {Start}, >=DATE(2023, 7, 1), {Start}, <=DATE(2023, 12, 30), {Project Leader}, [Project Leader Name]@row)

  • HI @Caleb W,

    If I am understanding your question correctly, you have an existing text/number column where you capture Scope, and you want join the value you enter in your Product column to the value in the scope column. Is that correct?

  • Caleb W
    Caleb W ✭✭✭✭

    Thank you for the quick response @Matthew J McAteer . I am looking to be able to take my Scope column and write out, for example, "Precast Install Mod 1" as the full scope and location of the item. The the formula I am using in another sheet to to count the days only allows me use the exact text. I am wanting to have some flexibility in my text but still be counted if it contains the key words.

  • Hi @Caleb W,

    I believe you would have to JOIN your Scope and Product Type Columns into a helper column, then you could use the HAS function to find the Product Type string in the joined column.

    I would need to see an example of how you have this set up to fully understand. Happy to take a look if you can post some screenshots.

    Matthew

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!