Formulas to set up KPI's for shipments requests

jorge.pacheco

Hi,

I need some help with forumals which is not my strength, thanks in advance.

What I need is to set up KPI’s measure for my shipments.

There are 3 parties involved that need to complete their own step.

Once each step is completed by each party (regulatory, WHS and Logistics). it will record a date highlighted in the bellow screenshot.

What I am trying to do is that from the Date Requested if Regulatory took 2 days for approval it is a fail, below that is a pass.

Then on the WHS Completion if they take more than 3 days since the RA Approval Date it is a fail, below that pass.

And same for Logistics completion, if it takes more than 2 days from WHS Completion date is a fail below that is a passed.

Then I also would like to have a count of days, how many days does it take from the Date Requested to the Logistics Completion Date, and how many days are in between, is there a formula for that?

Thanks

JIDEATTURRA

For the first part:

Make the RA KPI column a drop down, with the two options: Pass and Fail

Then the formula will be this:

=IF([RA Approval Date]@row - [Date Requested]@row < 2, "Pass", "Fail")

Make the WHS KPI column a drop down, with the two options: Pass and Fail

Then the formula will be this:

=IF([WHS Completion Date]@row - [RA Approval Date]@row < 3, "Pass", "Fail")

Make the MISC KPI column a drop down, with the two options: Pass and Fail

Then the formula will be this:

=IF([Logistics Completion Date]@row - [WHS Completion Date]@row < 2, "Pass", "Fail")

Right click on all the KPI columns and make the formula a column formula so it automatically calculates every line.

[Please upvote this if it helped, it helps me]

JIDEATTURRA

For the second part:

The number of days in-between is simply the below in a text/number column:

=[Logistics Completion Date] - [Date Requested]

[Please upvote this if it helped, it helps me]

jorge.pacheco

Hi Jideatturra,

Thank you so much! I really appreciate your help!

Just some additional comments as I am not getting it right with the requests that still don't have a date but it is showing "Pass" instead of keeping int in Blank.

For Example:

1. MISC 24453 is showing that Regulatory Affairs Status hasn't been Approved, that means that not date would be recorded just yet, even tho this is showing as a pass.
2. Same for the other references but for Warehouse Status, the WHS hasn't completed their part so not day is being recorded yet, but this is showing as a pass.

Is there any way I can keep in blank the ones that the date hasn't been recorded?

Thank you so much for your help!

JIDEATTURRA

Yeah, you would just add another condition mate, like so;

=IF([RA Approval Date]@row = "", "", IF([RA Approval Date]@row - [Date Requested]@row < 2, "Pass", "Fail"))

[Please upvote this if it helped, it helps me]

JIDEATTURRA

For second one;

=IF([WHS Completion Date]@row = "", "", IF([WHS Completion Date]@row - [RA Approval Date]@row < 3, "Pass", "Fail"))

[Please upvote this if it helped, it helps me]

JIDEATTURRA

For final one;

=IF([Logistics Completion Date]@row = "", "", IF([Logistics Completion Date]@row - [WHS Completion Date]@row < 2, "Pass", "Fail"))

[Please upvote this if it helped, it helps me]

jorge.pacheco

Hi! Getting back to this for extra help!

So far this formulas have been work perfect but we come up with an issue, we noticed that if a request was approved on a Friday end of the day and then we process on Monday (not working on weekends) this will cause a fail due to the "no more than 2 days to process" rule.

Is there any way to maybe get Smartsheet identify weekends days and exclude them from the formula so it doesn't make it a fail? not sure if this is possible but it would be great just to include the week days and not weekends.

Thanks in advance.

dojones

Use NetWorkDays to exclude weekends

=IF([Logistics Completion Date]@row = "", "", IF(NETWORKDAYS([Logistics Completion Date]@row, [WHS Completion Date]@row) < 2, "Pass", "Fail"))

You may also want to verify what is acceptable. Note that < 2 days has to be completed on the same day. If that is not what you want change formula per below to subractt 1 to make next day completion acceptable

=IF([Logistics Completion Date]@row = "", "", IF(NETWORKDAYS([Logistics Completion Date]@row, [WHS Completion Date]@row) - 1 < 2, "Pass", "Fail"))