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

Jamie Ide

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]

Jamie Ide

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!

Jamie Ide

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]

Jamie Ide

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]

Jamie Ide

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"))