Histogram on days past

Options

I'm trying to use a sheet that i have created to capture issues; but I'd like to create a histogram of days past of when they've been discovered.

7 Days past

14 Days past

30 days past

Etc.

Answers

  • Scott Peters
    Scott Peters ✭✭✭✭✭✭
    Options

    Hi @pdxhunley - This should be very easy to do:

    1. Add a column in your sheet with a column formula such as =IF([Discovery Date]@row < TODAY(-30), ">30 Days", IF([Discovery Date]@row < TODAY(-14), ">14 Days", IF([Discovery Date]@row < TODAY(-7), ">7 Days", "This Week")))
    2. Here's an example of what it would look like:
    3. Create a Smartsheet Report of that sheet, with a GROUP by the aging period
    4. Create a Chart Widget in your dashboard that points to the report you created

    This is just one way to accomplish this, but I hope it is helpful to you

  • pdxhunley
    Options

    @Scott Peters

    Thank you for that. I'm getting an error on this formula. It's only populating on some of the dates.

    =IF([Date Discovered]@row < TODAY(-120), ">120 Days", =IF([Date Discovered]@row < TODAY(-90), ">90 Days", =IF([Date Discovered]@row < TODAY(-60), ">60 Days", =IF([Date Discovered]@row < TODAY(-45), ">45 Days", =IF([Date Discovered]@row < TODAY(-30), ">30 Days", =IF([Date Discovered]@row < TODAY(-14), ">14 Days", =IF([Date Discovered]@row < TODAY(-7), ">7 Days", "This Week")))))))


  • Scott Peters
    Scott Peters ✭✭✭✭✭✭
    Options

    Hi @pdxhunley - On the day that you posted this, 6/22/23 was exactly 120 days apart from "today". Try changing from less than to less than or equal to:


  • mary twomley
    Options

    Is there a way to create this formula for between two time frames to make the histogram?

    Between 1-10 days; 11-20 days, 21-30 days, etc etc?

    Appreciate any info!

  • mary twomley
    Options

    @Scott Peters I was able to follow your steps to create a column with bucket labels and then create a grouped report. However, my dashboard is not allowing me to create

    a graph from this report. Any ideas?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!