Spreadsheet: 852327d1702396362-how-to-create-a-rule-for-a-cell-2024test-sheet
Sheet: Test

 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ON-CALL TIME RECORD
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | HOLIDAY RATE FORMULAS - CURRENT SALARY AND NEW SALARY
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Pay Type 700 Weekends @ $25.00/day |  |  |  |  |  |  |  |  | Pay Type 701 Holidays @ $40.00/Day |  |  |  |  |  |  |  |  | Pay Type 702           WEEK DAYS                        @ $15/day          TOTAL |  |  |  |  |  |  |  |  | Total Pay Type 702 Wk 1
Wk 1 | MON | TUES | WED | THUR | FRI | SAT | SUN | HRS | 1 day, 0:00:00 | 1 day, 12:00:00 | 2 days, 0:00:00 |  | Wk 1 | MON | TUES | WED | THUR | FRI | SAT | SUN | Total |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | WEEK 1
Date | 2008-12-04 00:00:00 |  |  |  |  |  |  |  |  |  | X |  | PTO |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | B15 |  |  |  |  |  |  |  |  | C15 |  |  |  |  |  |  |  |  | D15 |  |  |  |  |  |  |  |  | E15 |  |  |  |  |  |  |  |  | F15 |  |  |  |  |  |  |  |  | [NAME] |  |  |  |  |  |  |  |  | H15
Hours |  |  |  |  |  |  |  |  | Total Hrs Wkd |  |  |  | Annual
 |  |  |  |  |  |  |  |  | Total OT |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | WEEK 1 |  |  |  |  |  |  |  |  | WEEK 1 |  |  |  |  |  |  |  |  | WEEK 1 |  |  |  |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | 1 day, 16:00:00 | 1.666666666666667
Station |  |  |  |  |  |  |  |  |  |  |  |  | Sick
Holiday |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Total
Holiday New Rate
Wk 2 | MON | TUES | WED | THUR | FRI | SAT | SUN | WKD | 1 day, 0:00:00 | 1 day, 12:00:00 | 2 days, 0:00:00 |  | Wk 2 | MON | TUES | WED | THUR | FRI | SAT | SUN | Total |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | WEEK 2
Date |  |  |  |  |  |  |  |  |  | X |  |  | PTO |  |  |  |  |  |  |  |  |  |  |  | Pay Type 700 Weekends @ $25.00/day |  |  |  |  |  |  |  |  | Pay Type 701 Holidays @ $40.00/Day |  |  |  |  |  |  |  |  | Pay Type 702           WEEK DAYS                        @ $15/day          TOTAL |  |  |  |  |  |  |  |  | Total Pay Wk 2 Type 701 |  |  | B22 |  |  |  |  |  |  |  |  | C22 |  |  |  |  |  |  |  |  | D22 |  |  |  |  |  |  |  |  | E22 |  |  |  |  |  |  |  |  | F22 |  |  |  |  |  |  |  |  | G22 |  |  |  |  |  |  |  |  | H22
Hours |  |  |  |  |  |  |  |  | Total Hrs Wkd |  |  |  | Annual
 |  |  |  |  |  |  |  |  | Total OT |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | WEEK 2 |  |  |  |  |  |  |  |  | WEEK 2 |  |  |  |  |  |  |  |  | WEEK 2 |  |  |  |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | 1 day, 16:00:00
Station |  |  |  |  |  |  |  |  |  |  |  |  | Sick
Holiday |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Total
Holiday New Rate
Wk 3 | MON | TUES | WED | THUR | FRI | SAT | SUN | WKD | 1 day, 0:00:00 | 1 day, 12:00:00 | 2 days, 0:00:00 |  | Wk 3 | MON | TUES | WED | THUR | FRI | SAT | SUN | Total |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | WEEK 3
Date |  |  |  |  |  |  |  |  | X |  |  |  | PTO |  |  |  |  |  |  |  |  |  |  |  | Pay Type 700 Weekends @ $25.00/day |  |  |  |  |  |  |  |  | Pay Type 701 Holidays @ $40.00/Day |  |  |  |  |  |  |  |  | Pay Type 702           WEEK DAYS                        @ $15/day          TOTAL |  |  |  |  |  |  |  |  | Total Pay Wk 3 Type 701 |  |  | B29 |  |  |  |  |  |  |  |  | C29 |  |  |  |  |  |  |  |  | D29 |  |  |  |  |  |  |  |  | E29 |  |  |  |  |  |  |  |  | F29 |  |  |  |  |  |  |  |  | G29 |  |  |  |  |  |  |  |  | H29
Hours |  |  |  |  |  |  |  |  | Total Hrs Wkd |  |  |  | Annual
 |  |  |  |  |  |  |  |  | Total OT |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | WEEK 3 |  |  |  |  |  |  |  |  | WEEK 3 |  |  |  |  |  |  |  |  | WEEK 3 |  |  |  |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | 1 day, 16:00:00
Station |  |  |  |  |  |  |  |  |  |  |  |  | Sick
Holiday |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Total
Holiday New Rate
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | WEEK 4
The number of regular work hours for that week is based on which cell the "X" is placed under the 24:00, 36:00, 48:00. The entry in cells I12, I19, and I26 reflects the minimun regulary scheduled hours for that week and are formatted to show red if those minimum hours are not met. For cells V15, V22, and V29, these reflect the total hours worked for that week including Vac, Sick, or PTO time used to reach the minimum hours required. The cells are formatted to show red if the minimum required hours are not met. What I'm asking, using WK1 as the example, is how to make the formula assigned to I12 and V15 change based on if J11, K11, or L11 has an "X". In this example, Wk 1 reflects formulas for a 48 hr week, Wk 2 reflects formulas for a 36 hr week, and Wk 3 reflects formulas for a 24 hr week. I hope I've explained this sufficiantly. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Pay Type 700 Weekends @ $25.00/day |  |  |  |  |  |  |  |  | Pay Type 701 Holidays @ $40.00/Day |  |  |  |  |  |  |  |  | Pay Type 702           WEEK DAYS                        @ $15/day          TOTAL |  |  |  |  |  |  |  |  | Total Pay Wk 4 Type 701 |  |  | B36 |  |  |  |  |  |  |  |  | C36 |  |  |  |  |  |  |  |  | [NAME] |  |  |  |  |  |  |  |  | E36 |  |  |  |  |  |  |  |  | F36 |  |  |  |  |  |  |  |  | G36 |  |  |  |  |  |  |  |  | H36
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | WEEK 4 |  |  |  |  |  |  |  |  | WEEK 4 |  |  |  |  |  |  |  |  | WEEK 4 |  |  |  |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | 1 day, 16:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | WEEK 5
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Pay Type 700 Weekends @ $25.00/day |  |  |  |  |  |  |  |  | Pay Type 701 Holidays @ $40.00/Day |  |  |  |  |  |  |  |  | Pay Type 702           WEEK DAYS                        @ $15/day          TOTAL |  |  |  |  |  |  |  |  | Total Pay Wk 5 Type 701 |  |  | B43 |  |  |  |  |  |  |  |  | C43 |  |  |  |  |  |  |  |  | D43 |  |  |  |  |  |  |  |  | E43 |  |  |  |  |  |  |  |  | F43 |  |  |  |  |  |  |  |  | [NAME] |  |  |  |  |  |  |  |  | H43
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | WEEK 5 |  |  |  |  |  |  |  |  | WEEK 5 |  |  |  |  |  |  |  |  | WEEK 5 |  |  |  |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | New Rate Formula below: |  |  |  |  |  |  |  |  | 1 day, 16:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | OVERTIME NEW RATE |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | OVERTIME Current Rate |  |  |  |  | Regular Pay Old Rate
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | OVERTIME New Rate
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Current Pay |  |  |  |  | New Pay