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
-
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
-
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?
-
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 Thank you for the help on the formula. That did exactly what I was looking for.
-
Happy to help
-
@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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!