Excel Formula calculating threshold on a weekly basis

G

gd6noob

Board Regular
Joined
Oct 20, 2017
Messages
170
Office Version
  1. 2016
Platform
  1. Windows
Hi everyone,

Just had a question, I have a sheet that is used to calculate the theshold in percentage for each manager, to ensure their team stays within the 10% limit, which starts with current day. This is good.
Now Im looking for future state. e.g. Name 3 is taking May 1 off for 4 days, which includes Week 17 and 18. Name 4 is takin July 5 off for 27 days, which includes week 27 to week 30. I cant seem to figure out the formula to get this in column R to T.

HRRC Canada Time Off Tracker.xlsb
ABCDEFGHIJKLMNOPQRST
1LoginNameFirst Day MissedNumber of DaysEnd DateManagerEmployment TypeRegionApproval StatusType of leaveApprover CommentsBalanceManagerTotal HC0.1Week NumberTotal HC0.1
2Name 13/1/2022394/9/2022Manager 1ApprovedLOALOA not trackedTeam751.33%175
3Name 23/15/2022174/1/2022Manager 2ApprovedVacationVac not trackedManager 14Exceeds Target275
4Name 35/1/202245/5/2022Manager 3ApprovedVacationVac not trackedManager 200%375
5Name 47/5/2022278/1/2022Manager 4ApprovedLOALOA not trackedManager 380.00%475
6Manager 4210.00%575
7Manager 540.00%675
8Manager 6150.00%775
9Manager 7110.00%875
10Manager 8120.00%975
11Manager 900%1075
121175
131275
141375
151475
161575
171675
181775
191875
201975
212075
222175
232275
242375
252475
262575
272675
282775
292875
302975
313075
323175
333275
343375
353475
363575
373675
383775
393875
403975
414075
424175
434275
444375
454475
464575
474675
484775
494875
504975
515075
525175
535275
545375
2022
Cell Formulas
RangeFormula
D2:D5D2=[@[End Date]]-[@[First Day Missed]]
L2:L5L2=IF(J2='Do Not Delete'!$C$2,10-SUMIFS([Number of Days],[Login],[@Login],[Approval Status],'Do Not Delete'!$E$2,[Type of leave],'Do Not Delete'!$C$2)-SUMIFS([Number of Days],[Login],[@Login],[Approval Status],'Do Not Delete'!$E$2,[Type of leave],'Do Not Delete'!$C$4),IF(J2='Do Not Delete'!$C$3,2-COUNTIFS([Login],[@Login],[Approval Status],'Do Not Delete'!$E$2,[Type of leave],'Do Not Delete'!$C$3),IF(J2='Do Not Delete'!$C$4,3-SUMIFS([Number of Days],[Login],[@Login],[Approval Status],'Do Not Delete'!$E$2,[Type of leave],'Do Not Delete'!$C$4),IF(J2='Do Not Delete'!$C$5,5-SUMIFS([Number of Days],[Login],[@Login],[Approval Status],'Do Not Delete'!$E$2,[Type of leave],'Do Not Delete'!$C$5),IF(J2='Do Not Delete'!$C$6,3-SUMIFS([Number of Days],[Login],[@Login],[Approval Status],'Do Not Delete'!$E$2,[Type of leave],'Do Not Delete'!$C$6),IF(J2='Do Not Delete'!$C$7,"Vac not tracked",IF(J2='Do Not Delete'!$C$8,"LOA not tracked","Not Used")))))))
P2P2=IFERROR(IF((COUNTIFS(Table1[First Day Missed],"<="&TODAY(),Table1[End Date],">="&TODAY())/$O2)>$P$1,"Exceeds Target",COUNTIFS(Table1[First Day Missed],"<="&TODAY(),Table1[End Date],">="&TODAY())/$O2),"0%")
P3:P11P3=IFERROR(IF((COUNTIFS(Table1[Manager],$N3,Table1[First Day Missed],"<="&TODAY(),Table1[End Date],">="&TODAY())/$O3)>$P$1,"Exceeds Target",COUNTIFS(Table1[Manager],$N3,Table1[First Day Missed],"<="&TODAY(),Table1[End Date],">="&TODAY())/$O3),"0%")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G5Cell Valuecontains "Green"textNO
G5Cell Valuecontains "Blue"textNO
G4Cell Valuecontains "Green"textNO
G4Cell Valuecontains "Blue"textNO
G2:G3Cell Valuecontains "Green"textNO
G2:G3Cell Valuecontains "Blue"textNO
Cells with Data Validation
CellAllowCriteria
A2:A5Any value
B2:B5Any value
C2:C5Any value
D2:D5Any value
E2:E5Any value
N1:P1Any value
P2Any value
N3:P11Any value
G2:G5List='Do Not Delete'!$B$2:$B$3
H2:H5List='Do Not Delete'!$B$5:$B$7
I2:I5List='Do Not Delete'!$E$2
J2:J5List='Do Not Delete'!$C$2:$C$8
K2:K5Any value
L2:L5Any value


If more clarity is need, please let me know.

Regards,
Erik
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
You must log in or register to reply here.

Similar threads

A
  • Question Question
Loan Tracker with prior/missed payments
Replies
0
Views
112
aurelius33
A
N
  • Solved
Count days between to Table Ranges that falls in SLA intervals
Replies
4
Views
127
Nicha
N
JenniferMurphy
  • Question Question
Can someone help me understand Bayesian averages?
Replies
1
Views
175
FormulasOnly
F
JenniferMurphy
  • Solved
How to count the number of numbers in a given period?
2
Replies
16
Views
313
JenniferMurphy
JenniferMurphy
J
  • Solved
LET/VSTACK Help
Replies
2
Views
181
Jedi Master
J

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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