I want to return date ranges outside of the start date and end date columns.

Tyler Dick

I am trying to make a Smartsheet that records malfunctioning equipment, but also reports on dates when the system is fully functional.

For example, I have malfunctioning equipment that malfunctions from start date 7/1/20 to end date 7/15/20. How do I automate returning date ranges 6/1/20 to 6/30/20 and 7/16/20 to today? (6/1/20 would be the first day of inclusion for this data set) Or automating dates between the end of one malfunction and the beginning of another?

Paul Newcome

Are you able to provide a screenshot with mock data manually entered that shows what you are trying to accomplish?

Tyler Dick

Here is a snap shot of the setup. The far left column describes the component that is malfunctioning. The next two columns are the start date and the end date for the malfunction.

I created the next two columns with the formula '=[Date Start]x - 1' and '=[Date Start]x + 1', left to right respectively. This allows me to easily pull the start and end dates for when the system is not malfunctioning. I have manually pulled the dates that the system is functioning without malfunction using the far right two columns, but I still have to do that manually. I am hoping to automate this process.

Here is a snapshot of the product:

This shows the date ranges when the site is fully functional as I am compiling dates for a performance analysis.

Hope this helps, please let me know if further information will help find a solution.

Paul Newcome

Ok. So how would you determine the "Start Date" for the "before" where the "End Date" for the "before" would be [Date Start] - 1?