Work KPI Formula(s)

D

deyn

New Member
Joined
Mar 8, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I'm trying to produce a work sheet for my work KPI targets and our pass/fail success rate, which is made up of different requirements.

Job Priority (Column D)
Job Promotion Date (Column Y)
KPI Target (Column AI)
Job Complete Date (Column AJ)
KPI Pass/Fail (Column AK)

Our KPI targets are based on how long it takes up to complete a job based on the job priority.

P1/SOR/NMA (and blank cell) = End of the next day after job promotion. So if job promotion was today (21/03) the KPI target would be (22/03)
P2 = 48 hours after job promotion, (21/03) - (23/03)

So I think I need 2 formula's :

First Formula (Column AI)
To look at the Promotion date (Y) & Job Priority (D) to determine the KPI Target date

Second Formula (Column AK)
To determine off the job completed date (AJ) if we have passed or failed our KPI target - If we've passed, I would like "Pass" and if failed, I would like "Fail"

1679393284868.png
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
something like this?:

mr excel questions 15.xlsm
DJAIAJAK
1Job PriorityPromotion DateKPI TargetJob Completion DateKPI Pass/Fail
2P12023-03-152023-03-162023-03-18Fail
3P12023-03-152023-03-162023-03-18Fail
4P12023-03-172023-03-182023-03-18Pass
52023-03-172023-03-182023-03-18Pass
6P12023-03-152023-03-162023-03-18Fail
7P22023-03-192023-03-212023-03-18Pass
8SOR2023-03-192023-03-202023-03-18Pass
9SOR2023-03-192023-03-202023-03-18Pass
102023-03-162023-03-172023-03-18Fail
11P22023-03-172023-03-192023-03-18Pass
122023-03-182023-03-192023-03-18Pass
13P12023-03-142023-03-152023-03-18Fail
14P12023-03-162023-03-172023-03-18Fail
15NMA2023-03-192023-03-202023-03-18Pass
16SOR2023-03-172023-03-182023-03-18Pass
17SOR2023-03-172023-03-182023-03-18Pass
182023-03-172023-03-182023-03-18Pass
19SOR2023-03-172023-03-182023-03-18Pass
20SOR2023-03-172023-03-182023-03-18Pass
21SOR2023-03-172023-03-182023-03-18Pass
22P22023-03-162023-03-182023-03-18Pass
23P12023-03-142023-03-152023-03-18Fail
Sheet21
Cell Formulas
RangeFormula
AI2:AI23AI2=J2+IF(OR($D2="P1",$D2="SOR",$D2="NMA",$D2=""),1,2)
AK2:AK23AK2=IF($AJ2="","",IF(AJ2<=AI2,"Pass","Fail"))
 
Upvote 0
awoohaw said:
something like this?:

mr excel questions 15.xlsm
DJAIAJAK
1Job PriorityPromotion DateKPI TargetJob Completion DateKPI Pass/Fail
2P12023-03-152023-03-162023-03-18Fail
3P12023-03-152023-03-162023-03-18Fail
4P12023-03-172023-03-182023-03-18Pass
52023-03-172023-03-182023-03-18Pass
6P12023-03-152023-03-162023-03-18Fail
7P22023-03-192023-03-212023-03-18Pass
8SOR2023-03-192023-03-202023-03-18Pass
9SOR2023-03-192023-03-202023-03-18Pass
102023-03-162023-03-172023-03-18Fail
11P22023-03-172023-03-192023-03-18Pass
122023-03-182023-03-192023-03-18Pass
13P12023-03-142023-03-152023-03-18Fail
14P12023-03-162023-03-172023-03-18Fail
15NMA2023-03-192023-03-202023-03-18Pass
16SOR2023-03-172023-03-182023-03-18Pass
17SOR2023-03-172023-03-182023-03-18Pass
182023-03-172023-03-182023-03-18Pass
19SOR2023-03-172023-03-182023-03-18Pass
20SOR2023-03-172023-03-182023-03-18Pass
21SOR2023-03-172023-03-182023-03-18Pass
22P22023-03-162023-03-182023-03-18Pass
23P12023-03-142023-03-152023-03-18Fail
Sheet21
Cell Formulas
RangeFormula
AI2:AI23AI2=J2+IF(OR($D2="P1",$D2="SOR",$D2="NMA",$D2=""),1,2)
AK2:AK23AK2=IF($AJ2="","",IF(AJ2<=AI2,"Pass","Fail"))
Click to expand...

This just adds days. If you need to consider weekends or holidays, the WORKDAYS.INTL function should be used in column AI.
 
Upvote 0
awoohaw said:
something like this?:

mr excel questions 15.xlsm
DJAIAJAK
1Job PriorityPromotion DateKPI TargetJob Completion DateKPI Pass/Fail
2P12023-03-152023-03-162023-03-18Fail
3P12023-03-152023-03-162023-03-18Fail
4P12023-03-172023-03-182023-03-18Pass
52023-03-172023-03-182023-03-18Pass
6P12023-03-152023-03-162023-03-18Fail
7P22023-03-192023-03-212023-03-18Pass
8SOR2023-03-192023-03-202023-03-18Pass
9SOR2023-03-192023-03-202023-03-18Pass
102023-03-162023-03-172023-03-18Fail
11P22023-03-172023-03-192023-03-18Pass
122023-03-182023-03-192023-03-18Pass
13P12023-03-142023-03-152023-03-18Fail
14P12023-03-162023-03-172023-03-18Fail
15NMA2023-03-192023-03-202023-03-18Pass
16SOR2023-03-172023-03-182023-03-18Pass
17SOR2023-03-172023-03-182023-03-18Pass
182023-03-172023-03-182023-03-18Pass
19SOR2023-03-172023-03-182023-03-18Pass
20SOR2023-03-172023-03-182023-03-18Pass
21SOR2023-03-172023-03-182023-03-18Pass
22P22023-03-162023-03-182023-03-18Pass
23P12023-03-142023-03-152023-03-18Fail
Sheet21
Cell Formulas
RangeFormula
AI2:AI23AI2=J2+IF(OR($D2="P1",$D2="SOR",$D2="NMA",$D2=""),1,2)
AK2:AK23AK2=IF($AJ2="","",IF(AJ2<=AI2,"Pass","Fail"))
Click to expand...
YES!! That's amazing! Thank you so much :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
514
XLfriend777
XLfriend777
E
  • Question Question
Updating a Unique/Filter formula to find the correct column
Replies
3
Views
256
hagia_sofia
hagia_sofia
L
  • Question Question
Formula to determine KPI hours
Replies
3
Views
502
Lukma
L
O
  • Question Question
Count only one occurrence of all criteria being met
Replies
8
Views
1K
KRice
K
Jyggalag
  • Solved
Formula to calculate between dates
Replies
4
Views
647
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,910
Messages
6,175,316
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top