Mean Time Between Two Dates from Form Submission

Hi Community,

I'm attempting to find the average downtime of an asset between dates from a form submission for the month. For example below, I need to find the number of days between the "OOS Tag Removal Date" and "Date OOS Tag Applied", based only on all entries for "Asset ID" FD-001201 where the "Reason for OOS" is Failure, then average that number for each month. Honestly, I don't even know the best way approach this.

That average will then be provided on my metric sheet below under the MTBF for each month to trend reliability:

Any help setting up this formula would be greatly appreciated!

Thank you!

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I would start by adding a helper column in the source sheet that outputs the number of days.


    From there your metrics sheet would use an AVG/COLLECT combo to collect all of the numbers in the helper column based on your range/criteria sets and average them.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Ahh didn't even consider that, that makes sense. I appreciate the direction!

    Now, if I also wanted to also determine the time between the finalization of one asset (OOS Removed) to the next time an entry for that asset is put in (OOS Sticker applied), how would I be able to identify that difference? Or is there not a way?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide a screenshot of how that data would look in the sheet?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Of course, see below:

    Data will go into the MTBF row as a numerical digit under the correct based off the below (Also having trouble with that formula shown):

    So essentially its taking the date and calculating the the difference between the OOS Tag removal date and the the next log of Date OOS Tag applied for the specific asset. Theoretically I could use the difference between Date OOS Tag Applied but I run into the same issue of how to create formulas between rows.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    We would need some kind of unique identifier to match on. I see you have an Asset ID column, but in your screenshot you have dates circled for different assets. I also see you have the same asset listed 3 times with the same tag applied date but then you have two different tag removal dates.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Those are arbitrary entries from testing the form, the purpose of the circled items was to show the process I'm trying to perform.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. So do you have some kind of unique id that we can use to match on, or were you just wanting to go with the very next form entry?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!