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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
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
149
XLfriend777
XLfriend777
L
  • Question
Formula to determine KPI hours
Replies
3
Views
381
Lukma
L
O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
406
KRice
K
A
  • Solved
AVERAGEIFS with 4 criteria
2
Replies
16
Views
303
Fluff
Fluff
S
  • Question
Overdue dates based on Status
Replies
3
Views
551
spuri78
S

Forum statistics

Threads
1,214,594
Messages
6,120,436
Members
448,964
Latest member
Danni317

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