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

Options

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?

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

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

  • Tyler Dick
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!