Mean Time Between Two Dates from Form Submission

Bryan Letourneau

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!

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.

Bryan Letourneau

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

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

Bryan Letourneau

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

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.

Bryan Letourneau

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

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?