Is it possible to use IF function inside the Conditional Formatting formula?

Sh8dyDan · Dec 20, 2023

Hello,

I am working to create a 2-week timesheet for personal use to track hours and calculate pay to later compare to the actual pay stub. We have a non-rotating day shift and a rotating 12-hour shift. We have a list of observed holidays I'd like the row that a holiday falls on to have a colored background. The kicker is that non-rotating and rotating shift holidays can at times be observed on different days. I created two defined name lists for each shift with the dates of their holidays, HolidayNonRotation and HolidayRotating. I'd like to use a set of Radio Buttons "Non-Rotating Shift" and "Rotating Shift" to switch between formatting. I can not find a solution similar to my use or create one of my own that works.

I've tried the code below with no luck. When the formula is TRUE it always returns FALSE. Even when I change the state of the Radio Button. I have verified referenced cells are correct.

Excel Formula:

=IF(AB6=1,MATCH($Z6,HolidaysNonRotating,0),MATCH($Z6,HolidaysRotating,0)

Thank you in advance,
Dan N.

SanjayGMusafir · Dec 20, 2023

Check this and revert - Not sure it will work or not...

Excel Formula:

=And(AB6=1,MATCH($Z6,HolidaysNonRotating,0),MATCH($Z6,HolidaysRotating,0)

SanjayGMusafir · Dec 20, 2023

SanjayGMusafir said:
Check this and revert - Not sure it will work or not...

Excel Formula:

=And(AB6=1,MATCH($Z6,HolidaysNonRotating,0),MATCH($Z6,HolidaysRotating,0)

Just realized gave wrong solution

Sh8dyDan · Dec 20, 2023

SanjayGMusafir said:
Check this and revert - Not sure it will work or not...

Excel Formula:

=And(AB6=1,MATCH($Z6,HolidaysNonRotating,0),MATCH($Z6,HolidaysRotating,0)

No, it did not work.

Thank you,
Dan N.

SanjayGMusafir · Dec 20, 2023

This should work - Have to find way to bypass IF function

Excel Formula:

=OR(And(AB6=1,MATCH($Z6,HolidaysNonRotating,0)),And(AB6<>1,MATCH($Z6,HolidaysRotating,0)))

Sh8dyDan · Dec 20, 2023

SanjayGMusafir said:
This should work - Have to find way to bypass IF function

Excel Formula:

=OR(And(AB6=1,MATCH($Z6,HolidaysNonRotating,0)),And(AB6<>1,MATCH($Z6,HolidaysRotating,0)))

The formula logic appears to be good, but the highlights never change when changing the radio button.

Sh8dyDan · Dec 20, 2023

2023 Paycheck Calculator.xlsx

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

3

Hours Worked

Date Start

4

Scheduled Hours

Hours Worked

Worked Break

Early Start

Straight

Overtime 1.5

Overtime 2.0

Overtime 2.5

2nd Shift

2rd Shift 1.5

2rd Shift 2.0

3rd Shift

3rd Shift 1.5

3rd Shift 2.0

12/18/2023

5

6:30 AM

4:30 PM

37.97

0.20

0.30

0.40

0.60

0.80

Total

10 Hour / Shift

6

M

6:30 AM

4:30 PM

1

7

T

6:30 AM

4:30 PM

8

W

6:30 AM

4:30 PM

9

T

6:30 AM

4:30 PM

10

F

-0.63

-0.19

12/22/2023

11

S

12/23/2023

12

S

12/24/2023

13

M

6:30 AM

4:30 PM

14

T

6:30 AM

4:30 PM

15

W

6:30 AM

4:30 PM

16

T

6:30 AM

4:30 PM

17

F

12/29/2023

18

S

12/30/2023

19

S

12/31/2023

20

3041.41

21

Total Hours

Straight

Overtime 1.5

Overtime 2.0

Overtime 2.5

2nd Shift

2rd Shift 1.5

2rd Shift 2.0

3rd Shift

3rd Shift 1.5

3rd Shift 2.0

22

23

Straight Pay

OT 1.5 Pay

OT 2.0 Pay

OT 2.5 Pay

Total Pay

24

NEW W-4

Cell Formulas
Range	Formula
O5	O5	=N5*1.5
P5	P5	=N5*2
Q5	Q5	=N5*2.5
N6:N9,N13:N16	N6	=IF(K6=0,,MIN($K$5,K6))
O6:O9,O13:O16	O6	=IF(K6=0,,IF(AND(K6>=$K$5,AE6=TRUE,AF6=TRUE), (K6-N6)+0.5+2, IF(AND(K6>=$K$5,AE6=TRUE,AF6=FALSE), (K6-N6)+0.5, IF(AND(K6>=$K$5,AE6=FALSE,AF6=TRUE), (K6-N6)+2, IF(AND(K6>=$K$5,AE6=FALSE,AF6=FALSE), K6-N6, IF(AND(K6<$K$5,AE6=TRUE,AF6=TRUE),2, IF(AND(K6<$K$5,AE6=TRUE,AF6=FALSE),, IF(AND(K6<$K$5,AE6=FALSE,AF6=TRUE),2,))))))))
N10,N17	N10	=IF(SUM(N6:N9)>=40,,IF(SUM(N6:N9)+K10<=40,IF(40-SUM(N6:N9)<$K$5,MIN($K$5,K10),K10),40-SUM(N6:N9)))
O10:O11,O17:O18	O10	=IF(K10=0,,IF(AND(K10>=$K$5,AE10=TRUE,AF10=TRUE),IF(K10<$K$5,,(K10-N10)+0.5+2),IF(AND(K10>=$K$5,AE10=TRUE,AF10=FALSE),IF(K10<$K$5,,(K10-N10)+0.5),IF(AND(K10>=$K$5,AE10=FALSE,AF10=TRUE),(K10-N10)+2,IF(AND(K10>=$K$5,AE10=FALSE,AF10=FALSE),K10-N10,IF(AND(K10<$K$5,AE10=TRUE,AF10=TRUE),(K10-N10)+2,IF(AND(K10<$K$5,AE10=TRUE,AF10=FALSE),(K10-N10),IF(AND(K10<$K$5,AE10=FALSE,AF10=TRUE),(K10-N10)+2,IF(AND(K10<$K$5,AE10=FALSE,AF10=FALSE),K10-N10)))))))))
N11,N18	N11	=IF(SUM(N6:N10)>=40,,IF(SUM(N6:N10)+K11<=40,IF(40-SUM(N6:N10)<$K$5,MIN($K$5,K11),K11),40-SUM(N6:N10)))
R6:R9,R13:R16	R6	=IF(J6>=$T$34,IF((J6-I6)24>=$K$5,IF(I6+($K$5/24)<$T$34,,((I6+($K$5/24))-$T$34)24),IF(J6>=$T$35,IF(I6>=$T$34,($T$35-I6)24,($T$35-$T$34)24),IF(I6>=$T$34,(J6-I6)24,(J6-$T$34)24))),)
S6:S9,S11,S13:S18	S6	=IF(J6>=$T$34,IF((J6-I6)24>=$K$5,IF(I6+($K$5/24)<$T$34,IF(J6>=$T$35,($T$35-$T$34)24,(J6-$T$34)24),IF(J6>=$T$35,IF(I6>=$T$34,($T$35-I6)24,($T$35-$T$34)24),IF(I6>=$T$34,(J6-I6)24,(J6-(I6+($K$5/24)))*24))),),)
R10	R10	=IF(SUM(N6:N9)>=40,,IF(SUM(N6:N9)+K10<40,IF(J10<$T$34,,IF(J10<T35,(J10-$T$34)24,($T$35-$T$34)24)),IF(I10+((40-SUM(N6:N9))/24)<$T$34,,IF(I10+((40-SUM(N6:N9))/24)<$T$35,((I10+((40-SUM(N6:N9))/24))-$T$34)24,($T$35-$T$34)24))))
S10	S10	=IF(I10+((40-SUM(N6:N9))/24)<S35, IF(I10+((40-SUM(N6:N9))/24)<S35, IF(I10+((40-SUM(N6:N9))/24)<T35, J10-S35, T35-S35),2222),1111)
R11,R18	R11	=IF(SUM(N6:N10)>=40,,IF(SUM(N6:N10)+K11<40,IF(J11<$T$34,,IF(J11<$T$35,(J11-$T$34)24,($T$35-$T$34)24)),IF(I11+((40-SUM(N6:N10))/24)<$T$34,,IF(I11+((40-SUM(N6:N10))/24)<$T$35,((I11+((40-SUM(N6:N10))/24))-$T$34)*24,$T$35-$T$34))))
U6:U9,U13:U16	U6	=IF(K6=0,,IF(OR(I6<$S$34,J6>=$S$36),IF(AND(I6<$S$34,J6>=$S$36),($S$34-I6)24,IF(I6<$S$34,($S$34-I6)24,IF((J6-I6)24>=$K$5,IF(I6+($K$5/24)>$S$36,((I6+($K$5/24))-$S$36)24,),(J6-$S$36)*24))),))
V6:V11,V13:V18	V6	=IF(K6=0,,IF(OR(I6<$S$34,J6>=$S$36),IF(AND(I6<$S$34,J6>=$S$36),(($T$36-I6)+(J6-$S$36))24,IF(I6<$S$34,,IF((J6-I6)24>=$K$5,IF(I6+($K$5/24)>$S$36,((I6+($K$5/24))-$S$36)24,(J6-$S$36)24),(J6-$S$36)*24))),))
U10,U17	U10	=IF(SUM(N6:N9)>=40,,IF(K10=0,,IF(OR(I10<$S$34,J10>=$S$36),IF(AND(I10<$S$34,J10>=$S$36),($S$34-I10)24,IF(I10<$S$34,($S$34-I10)24,IF((J10-I10)24>=$K$5,IF(I10+($K$5/24)>$S$36,((I10+($K$5/24))-$S$36)24,),(J10-$S$36)*24))),)))
U11,U18	U11	=IF(SUM(N6:N10)>=40,,IF(K11=0,,IF(OR(I11<$S$34,J11>=$S$36),IF(AND(I11<$S$34,J11>=$S$36),($S$34-I11)24,IF(I11<$S$34,($S$34-I11)24,IF((J11-I11)24>=$K$5,IF(I11+($K$5/24)>$S$36,((I11+($K$5/24))-$S$36)24,),(J11-$S$36)*24))),)))
K5:K9,K11:K16	K5	=(J5-I5)*24
N12,N19	N12	=IF(AND(K6>=4,K7>=4,K8>=4,K9>=4,K10>=4,K11>=4),,IF(SUM(N6:N11)>=40,,IF(SUM(N6:N11)+K12<=40,IF(40-SUM(N6:N11)<$K$5,MIN($K$5,K12),K12),40-SUM(N6:N11))))
O12,O19	O12	=IF(K12=0,,IF(AND(K6>=4,K7>=4,K8>=4,K9>=4,K10>=4,K11>=4),,IF(AND(K12>=$K$5,AE12=TRUE,AF12=TRUE),IF(K12<$K$5,,(K12-N12)+0.5+2),IF(AND(K12>=$K$5,AE12=TRUE,AF12=FALSE),IF(K12<$K$5,,(K12-N12)+0.5),IF(AND(K12>=$K$5,AE12=FALSE,AF12=TRUE),(K12-N12)+2,IF(AND(K12>=$K$5,AE12=FALSE,AF12=FALSE),K12-N12,IF(AND(K12<$K$5,AE12=TRUE,AF12=TRUE),(K12-N12)+2,IF(AND(K12<$K$5,AE12=TRUE,AF12=FALSE),(K12-N12),IF(AND(K12<$K$5,AE12=FALSE,AF12=TRUE),(K12-N12)+2,IF(AND(K12<$K$5,AE12=FALSE,AF12=FALSE),K12-N12))))))))))
P12,P19	P12	=IF(AND(K6>=4,K7>=4,K8>=4,K9>=4,K10>=4,K11>=4),IF(AND(K12>=$K$5,AE12=TRUE,AF12=TRUE),IF(K12<$K$5,,(K12-N12)+0.5+2),IF(AND(K12>=$K$5,AE12=TRUE,AF12=FALSE),IF(K12<$K$5,,(K12-N12)+0.5),IF(AND(K12>=$K$5,AE12=FALSE,AF12=TRUE),(K12-N12)+2,IF(AND(K12>=$K$5,AE12=FALSE,AF12=FALSE),K12-N12,IF(AND(K12<$K$5,AE12=TRUE,AF12=TRUE),(K12-N12)+2,IF(AND(K12<$K$5,AE12=TRUE,AF12=FALSE),(K12-N12),IF(AND(K12<$K$5,AE12=FALSE,AF12=TRUE),(K12-N12)+2,IF(AND(K12<$K$5,AE12=FALSE,AF12=FALSE),K12-N12)))))))),)
R12,R19	R12	=IF(AND(K6>=4,K7>=4,K8>=4,K9>=4,K10>=4,K11>=4),,IF(SUM(N6:N11)>=40,,IF(SUM(N6:N11)+K12<40,IF(J12<$T$34,,IF(J12<$T$35,(J12-$T$34)24,($T$35-$T$34)24)),IF(I12+((40-SUM(N6:N11))/24)<$T$34,,IF(I12+((40-SUM(N6:N11))/24)<$T$35,((I12+((40-SUM(N6:N11))/24))-$T$34)*24,$T$35-$T$34)))))
S12,S19	S12	=IF(AND(K6>=4,K7>=4,K8>=4,K9>=4,K10>=4,K11>=4),,IF(J12>=$T$34,IF((J12-I12)24>=$K$5,IF(I12+($K$5/24)<$T$34,IF(J12>=$T$35,($T$35-$T$34)24,(J12-$T$34)24),IF(J12>=$T$35,IF(I12>=$T$34,($T$35-I12)24,($T$35-$T$34)24),IF(I12>=$T$34,(J12-I12)24,(J12-(I12+($K$5/24)))*24))),),))
T12,T19	T12	=IF(AND(K6>=4,K7>=4,K8>=4,K9>=4,K10>=4,K11>=4),IF(J12>=$T$34,IF((J12-I12)24>=$K$5,IF(I12+($K$5/24)<$T$34,IF(J12>=$T$35,($T$35-$T$34)24,(J12-$T$34)24),IF(J12>=$T$35,IF(I12>=$T$34,($T$35-I12)24,($T$35-$T$34)24),IF(I12>=$T$34,(J12-I12)24,(J12-(I12+($K$5/24)))*24))),),),)
U12,U19	U12	=IF(AND(K6>=4,K7>=4,K8>=4,K9>=4,K10>=4,K11>=4),,IF(SUM(N6:N11)>=40,,IF(K12=0,,IF(OR(I12<$S$34,J12>=$S$36),IF(AND(I12<$S$34,J12>=$S$36),($S$34-I12)24,IF(I12<$S$34,($S$34-I12)24,IF((J12-I12)24>=$K$5,IF(I12+($K$5/24)>$S$36,((I12+($K$5/24))-$S$36)24,),(J12-$S$36)*24))),))))
V12,V19	V12	=IF(AND(K6>=4,K7>=4,K8>=4,K9>=4,K10>=4,K11>=4),,IF(K12=0,,IF(OR(I12<$S$34,J12>=$S$36),IF(AND(I12<$S$34,J12>=$S$36),(($T$36-I12)+(J12-$S$36))24,IF(I12<$S$34,,IF((J12-I12)24>=$K$5,IF(I12+($K$5/24)>$S$36,((I12+($K$5/24))-$S$36)24,(J12-$S$36)24),(J12-$S$36)*24))),)))
W12,W19	W12	=IF(AND(K6>=4,K7>=4,K8>=4,K9>=4,K10>=4,K11>=4),IF(K12=0,,IF(OR(I12<$S$34,J12>=$S$36),IF(AND(I12<$S$34,J12>=$S$36),(($T$36-I12)+(J12-$S$36))24,IF(I12<$S$34,,IF((J12-I12)24>=$K$5,IF(I12+($K$5/24)>$S$36,((I12+($K$5/24))-$S$36)24,(J12-$S$36)24),(J12-$S$36)*24))),)),)
R17	R17	=IF(SUM(N13:N16)>=40,,IF(SUM(N13:N16)+K17<40,IF(J17<$T$34,,IF(J17<#REF!,(J17-$T$34)24,($T$35-$T$34)24)),IF(I17+((40-SUM(N13:N16))/24)<$T$34,,IF(I17+((40-SUM(N13:N16))/24)<$T$35,((I17+((40-SUM(N13:N16))/24))-$T$34)*24,$T$35-$T$34))))
Z6	Z6	=Z4
Z7	Z7	=$Z$4+1
Z8	Z8	=$Z$4+2
Z9	Z9	=$Z$4+3
Z10	Z10	=$Z$4+4
Z11	Z11	=$Z$4+5
Z12	Z12	=$Z$4+6
Z13	Z13	=$Z$4+7
Z14	Z14	=$Z$4+8
Z15	Z15	=$Z$4+9
Z16	Z16	=$Z$4+10
Z17	Z17	=$Z$4+11
Z18	Z18	=$Z$4+12
Z19	Z19	=$Z$4+13
X6:X19	X6	=(N6$N$5)+(O6$O$5)+(P6$P$5)+(Q6$Q$5)+(R6$R$5)+(S6$S$5)+(T6$T$5)+(U6$U$5)+(V6$V$5)+(W6$W$5)
X20	X20	=SUM(X6:X19)
K22,N22:W22	K22	=SUM(K6:K19)
N24:W24	N24	=N22*N5
X24	X24	=SUM(N24:W30)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I6:X19	Expression	=IF(AB6=1,MATCH($Z6,HolidaysNonRotating,0),MATCH($Z6,HolidaysRotating,0))	text	NO

Sh8dyDan · Dec 20, 2023

Did not mean to post this.

Sh8dyDan · Dec 20, 2023

Actually, it might help. This is a neat add-in.

SanjayGMusafir · Dec 21, 2023

Check this and revert - I think formula needs slight modification

Excel Formula:

=OR(And($AB$6=1,MATCH($Z6,HolidaysNonRotating,0)),And($AB$6<>1,MATCH($Z6,HolidaysRotating,0)))

Is it possible to use IF function inside the Conditional Formatting formula?

Sh8dyDan

New Member

Excel Facts

SanjayGMusafir

Well-known Member

SanjayGMusafir

Well-known Member

Sh8dyDan

New Member

SanjayGMusafir

Well-known Member

Sh8dyDan

New Member

Sh8dyDan

New Member

Sh8dyDan

New Member

Sh8dyDan

New Member

SanjayGMusafir

Well-known Member

Similar threads

Forum statistics

Share this page

Is it possible to use IF function inside the Conditional Formatting formula?

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

New Member

New Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock