Creat formula to evaluate date difference in working days

David Clunie

Hi All

I'm looking for inspiration on creating a formula on separate sheet looking at data and dates from another. For this example, I have left on one sheet to demonstrate.

I essentially have to complete all my projects 10 working days before Bom S Date starts and if not I need to flag up the QTY of projects and which ones by project ID number. I also use a status filed to flag if a project has not started, In progress and Completed.

Please see the example.

L_123

You can make stacked if statements to add multiple criteria, you can use contains to search for text inside of other text, there are infinite ways to build conditionals inside of smartsheet. An example would be

=if([Project Status]@row = "Finished", "",if(and(networkdays(finish@row,today())<10,contains("BB",[project id]@row)), "The project is due within the next 10 days, and is a BB project"

You can use the collect formula to analyze entire columns at a time and return values based on one or more criteria,

=join(collect({Project ID},{Project Status},not(@cell = "Complete")),", ")

You can do just about any sort of output you want. If you explain a bit more about what criteria/output you want beyond 10 business days I can go into more detail

L_123

You could try the networkdays formula.

=If(networkdays(start@row,today())>10,1,0)

https://help.smartsheet.com/function/networkdays

David Clunie

Thanks, I already have that type of function, but I need it to look at multiple columns and, if true, then report the result. So not just the dates?

And another cell to gather information on projects. as a concatenated string

L_123

You can make stacked if statements to add multiple criteria, you can use contains to search for text inside of other text, there are infinite ways to build conditionals inside of smartsheet. An example would be

=if([Project Status]@row = "Finished", "",if(and(networkdays(finish@row,today())<10,contains("BB",[project id]@row)), "The project is due within the next 10 days, and is a BB project"

You can use the collect formula to analyze entire columns at a time and return values based on one or more criteria,

=join(collect({Project ID},{Project Status},not(@cell = "Complete")),", ")

You can do just about any sort of output you want. If you explain a bit more about what criteria/output you want beyond 10 business days I can go into more detail