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!
Answers
-
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!
-
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?
-
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!
-
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.
-
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!
-
Those are arbitrary entries from testing the form, the purpose of the circled items was to show the process I'm trying to perform.
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!