Nested IFs? Rolling formula for sick paid / unpaid

L

LucieLiskova

New Member
Joined
Jan 19, 2017
Messages
17
Hi,

I have a question. I'm trying to create a formula to calculate my sick paid and sick unpaid. The allowance for the whole year is 3 days paid=24 hours. The rest needs to go to unpaid column. However, in each period this rule needs to be checked for in the previous period as well. So as per the example - in week 1 I will have 16 paid sick (the code for sick leave is 'sl' and needs to be multiplied by 8 for the full day). In week 2 I will have only 8 hrs because it already checked for the week before and 2 sick leave days were used. In week 3 I will have 8 hrs in unpaid sick because it has already been used. I need to be able to use this as a "rolling rule" for the whole year, where it's basically checked week by week.

Thank you.


Example.xlsx
CDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAY
4WEEK 1WEEK 2WEEK 3
6MonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSun
702/01/202303/01/202304/01/202305/01/202306/01/202307/01/2023#######09/01/202310/01/202311/01/202312/01/202313/01/202314/01/2023#######16/01/202317/01/202318/01/202319/01/202320/01/202321/01/2023#######
8Employee IDShiftO/TShiftO/TShiftO/TShiftO/TShiftO/TFirst 4 hrsO/TO/TTOTALSick PaidSick UnpaidShiftO/TShiftO/TShiftO/TShiftO/TShiftO/TFirst 4 hrsO/TO/TTOTALSick PaidSick UnpaidShiftO/TShiftO/TShiftO/TShiftO/TShiftO/TFirst 4 hrsO/TO/TTOTALSick PaidSick Unpaid
918slsl8824.0016.00SL888832.008.00SL888832.008.00
Construction - Payroll Hrs 2023
Cell Formulas
RangeFormula
Q9,AW9,AG9Q9=LET(c,39.5,al,COUNTIF(D9:P9,"AL.5")*4+COUNTIF(D9:P9,"AL")*8,bh,COUNTIF(D9:P9,"BH")*8,basic,SUMIF($D$8:$M$8,"Shift",D9:M9)+al+bh,ota,SUMIF($D$8:$M$8,"O/T",D9:M9)+N9,otb,O9+P9,otacount,IF(ota=0,0,MAX(0,ota-(c-basic))),otbcount,IF(otb=0,0,MAX(0,otb-(c-basic-(ota-otacount)))),basic+(ota-otacount)+(otacount*1.5)+(otb-otbcount)+(otbcount*2))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AP9Cell Valuecontains "BH"textNO
AP9Cell Valuecontains "PL"textNO
AP9Cell Valuecontains "SL"textNO
AP9Cell Valuecontains "AL"textNO
AN9Cell Valuecontains "BH"textNO
AN9Cell Valuecontains "PL"textNO
AN9Cell Valuecontains "SL"textNO
AN9Cell Valuecontains "AL"textNO
AL9Cell Valuecontains "BH"textNO
AL9Cell Valuecontains "PL"textNO
AL9Cell Valuecontains "SL"textNO
AL9Cell Valuecontains "AL"textNO
AR9Cell Valuecontains "BH"textNO
AR9Cell Valuecontains "PL"textNO
AR9Cell Valuecontains "SL"textNO
AR9Cell Valuecontains "AL"textNO
AJ9Cell Valuecontains "BH"textNO
AJ9Cell Valuecontains "PL"textNO
AJ9Cell Valuecontains "SL"textNO
AJ9Cell Valuecontains "AL"textNO
Z9Cell Valuecontains "BH"textNO
Z9Cell Valuecontains "PL"textNO
Z9Cell Valuecontains "SL"textNO
Z9Cell Valuecontains "AL"textNO
X9Cell Valuecontains "BH"textNO
X9Cell Valuecontains "PL"textNO
X9Cell Valuecontains "SL"textNO
X9Cell Valuecontains "AL"textNO
V9Cell Valuecontains "BH"textNO
V9Cell Valuecontains "PL"textNO
V9Cell Valuecontains "SL"textNO
V9Cell Valuecontains "AL"textNO
AB9Cell Valuecontains "BH"textNO
AB9Cell Valuecontains "PL"textNO
AB9Cell Valuecontains "SL"textNO
AB9Cell Valuecontains "AL"textNO
T9Cell Valuecontains "BH"textNO
T9Cell Valuecontains "PL"textNO
T9Cell Valuecontains "SL"textNO
T9Cell Valuecontains "AL"textNO
L9Cell Valuecontains "BH"textNO
L9Cell Valuecontains "PL"textNO
L9Cell Valuecontains "SL"textNO
L9Cell Valuecontains "AL"textNO
J9Cell Valuecontains "BH"textNO
J9Cell Valuecontains "PL"textNO
J9Cell Valuecontains "SL"textNO
J9Cell Valuecontains "AL"textNO
H9Cell Valuecontains "BH"textNO
H9Cell Valuecontains "PL"textNO
H9Cell Valuecontains "SL"textNO
H9Cell Valuecontains "AL"textNO
F9Cell Valuecontains "BH"textNO
F9Cell Valuecontains "PL"textNO
F9Cell Valuecontains "SL"textNO
F9Cell Valuecontains "AL"textNO
D9Cell Valuecontains "BH"textNO
D9Cell Valuecontains "PL"textNO
D9Cell Valuecontains "SL"textNO
D9Cell Valuecontains "AL"textNO
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Would this work for you ?

Copied across to total section for each week.

R9
Excel Formula: 
=LET(prevSickPd,SUMIFS($D9:Q9,$D$8:Q$8,"Sick Paid"),
          totalSickPd,COUNTIFS($D9:Q9,"SL",$D$8:Q$8,"Shift")*8,
          currentSickPd,  totalSickPd - prevSickPd,
          MEDIAN(24-prevSickPd,currentSickPd,0))

S9
Excel Formula: 
=LET(prevSickPd,SUMIFS($D9:Q9,$D$8:Q$8,"Sick Paid"),
          totalSickPd,COUNTIFS($D9:Q9,"SL",$D$8:Q$8,"Shift")*8,
          currentSickPd,  totalSickPd - prevSickPd,
          currentSickPd-R9)
 
Upvote 0
Solution
Alex Blakenburg said:
Would this work for you ?

Copied across to total section for each week.

R9
Excel Formula: 
=LET(prevSickPd,SUMIFS($D9:Q9,$D$8:Q$8,"Sick Paid"),
          totalSickPd,COUNTIFS($D9:Q9,"SL",$D$8:Q$8,"Shift")*8,
          currentSickPd,  totalSickPd - prevSickPd,
          MEDIAN(24-prevSickPd,currentSickPd,0))

S9
Excel Formula: 
=LET(prevSickPd,SUMIFS($D9:Q9,$D$8:Q$8,"Sick Paid"),
          totalSickPd,COUNTIFS($D9:Q9,"SL",$D$8:Q$8,"Shift")*8,
          currentSickPd,  totalSickPd - prevSickPd,
          currentSickPd-R9)
Click to expand...
That works great, Alex. Thanks a lot. : )
 
Upvote 0
Alex Blakenburg said:
Thanks for letting me know. Glad I could help.
Click to expand...
Hi Alex, just realised now that if I have 24 hrs sick paid in R9, it then copies further to the next week. In the week after it should just ignore the sick paid (or go to sick unpaid if the employee is sick). It's the same for the sick unpaid - if they have sick unpaid in week 2, it automatically copies to week 3, etc. Is there any way around that?
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
INDEX / MATCH Used Horizontally & Looking for a Value Based on a Dropdown List
Replies
1
Views
474
LucieLiskova
L
HappyChappy
  • Question
Timesheet Very Basic needs an expert eye
Replies
1
Views
203
HappyChappy
HappyChappy
C
  • Question
3 payment some payments not made or paid in full
Replies
1
Views
396
CrashOD
C

Forum statistics

Threads
1,215,086
Messages
6,123,043
Members
449,092
Latest member
ikke

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