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.
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 | |||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW | AX | AY | |||
4 | WEEK 1 | WEEK 2 | WEEK 3 | ||||||||||||||||||||||||||||||||||||||||||||||||
6 | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | ||||||||||||||||||||||||||||||
7 | 02/01/2023 | 03/01/2023 | 04/01/2023 | 05/01/2023 | 06/01/2023 | 07/01/2023 | ####### | 09/01/2023 | 10/01/2023 | 11/01/2023 | 12/01/2023 | 13/01/2023 | 14/01/2023 | ####### | 16/01/2023 | 17/01/2023 | 18/01/2023 | 19/01/2023 | 20/01/2023 | 21/01/2023 | ####### | ||||||||||||||||||||||||||||||
8 | Employee ID | Shift | O/T | Shift | O/T | Shift | O/T | Shift | O/T | Shift | O/T | First 4 hrs | O/T | O/T | TOTAL | Sick Paid | Sick Unpaid | Shift | O/T | Shift | O/T | Shift | O/T | Shift | O/T | Shift | O/T | First 4 hrs | O/T | O/T | TOTAL | Sick Paid | Sick Unpaid | Shift | O/T | Shift | O/T | Shift | O/T | Shift | O/T | Shift | O/T | First 4 hrs | O/T | O/T | TOTAL | Sick Paid | Sick Unpaid | ||
9 | 1 | 8 | sl | sl | 8 | 8 | 24.00 | 16.00 | SL | 8 | 8 | 8 | 8 | 32.00 | 8.00 | SL | 8 | 8 | 8 | 8 | 32.00 | 8.00 | |||||||||||||||||||||||||||||
Construction - Payroll Hrs 2023 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
Q9,AW9,AG9 | Q9 | =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 | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
AP9 | Cell Value | contains "BH" | text | NO |
AP9 | Cell Value | contains "PL" | text | NO |
AP9 | Cell Value | contains "SL" | text | NO |
AP9 | Cell Value | contains "AL" | text | NO |
AN9 | Cell Value | contains "BH" | text | NO |
AN9 | Cell Value | contains "PL" | text | NO |
AN9 | Cell Value | contains "SL" | text | NO |
AN9 | Cell Value | contains "AL" | text | NO |
AL9 | Cell Value | contains "BH" | text | NO |
AL9 | Cell Value | contains "PL" | text | NO |
AL9 | Cell Value | contains "SL" | text | NO |
AL9 | Cell Value | contains "AL" | text | NO |
AR9 | Cell Value | contains "BH" | text | NO |
AR9 | Cell Value | contains "PL" | text | NO |
AR9 | Cell Value | contains "SL" | text | NO |
AR9 | Cell Value | contains "AL" | text | NO |
AJ9 | Cell Value | contains "BH" | text | NO |
AJ9 | Cell Value | contains "PL" | text | NO |
AJ9 | Cell Value | contains "SL" | text | NO |
AJ9 | Cell Value | contains "AL" | text | NO |
Z9 | Cell Value | contains "BH" | text | NO |
Z9 | Cell Value | contains "PL" | text | NO |
Z9 | Cell Value | contains "SL" | text | NO |
Z9 | Cell Value | contains "AL" | text | NO |
X9 | Cell Value | contains "BH" | text | NO |
X9 | Cell Value | contains "PL" | text | NO |
X9 | Cell Value | contains "SL" | text | NO |
X9 | Cell Value | contains "AL" | text | NO |
V9 | Cell Value | contains "BH" | text | NO |
V9 | Cell Value | contains "PL" | text | NO |
V9 | Cell Value | contains "SL" | text | NO |
V9 | Cell Value | contains "AL" | text | NO |
AB9 | Cell Value | contains "BH" | text | NO |
AB9 | Cell Value | contains "PL" | text | NO |
AB9 | Cell Value | contains "SL" | text | NO |
AB9 | Cell Value | contains "AL" | text | NO |
T9 | Cell Value | contains "BH" | text | NO |
T9 | Cell Value | contains "PL" | text | NO |
T9 | Cell Value | contains "SL" | text | NO |
T9 | Cell Value | contains "AL" | text | NO |
L9 | Cell Value | contains "BH" | text | NO |
L9 | Cell Value | contains "PL" | text | NO |
L9 | Cell Value | contains "SL" | text | NO |
L9 | Cell Value | contains "AL" | text | NO |
J9 | Cell Value | contains "BH" | text | NO |
J9 | Cell Value | contains "PL" | text | NO |
J9 | Cell Value | contains "SL" | text | NO |
J9 | Cell Value | contains "AL" | text | NO |
H9 | Cell Value | contains "BH" | text | NO |
H9 | Cell Value | contains "PL" | text | NO |
H9 | Cell Value | contains "SL" | text | NO |
H9 | Cell Value | contains "AL" | text | NO |
F9 | Cell Value | contains "BH" | text | NO |
F9 | Cell Value | contains "PL" | text | NO |
F9 | Cell Value | contains "SL" | text | NO |
F9 | Cell Value | contains "AL" | text | NO |
D9 | Cell Value | contains "BH" | text | NO |
D9 | Cell Value | contains "PL" | text | NO |
D9 | Cell Value | contains "SL" | text | NO |
D9 | Cell Value | contains "AL" | text | NO |