Count Days and Durations

Caleb W

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.

Matthew J McAteer

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 😊

Matthew J McAteer

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

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

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

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

Matthew J McAteer

Happy to help

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)

Matthew J McAteer

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

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.

Matthew J McAteer

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