Not including a Cell in a formula

[JDSOuth49]

Summary of my issue:

CELL P15 = USED TO ENTER TRAVEL HOURS (CALCULATED AS STRAIGH TIME ONLY)



CELL Q15 = =IF(WEEKDAY(B4)<=5,IF(S15>8,8,S15),0)+IF(P15<>"",P15,0) **WORKS CORRECTLY-NO ISSUES**



CELL R15 = CALCULATES OVERTIME (REFERENCES CELL S15 à ANY TIME OVER 8 HOURS IS PUT INTO CELL R15 [CELL R15 FORMULA= =IF(WEEKDAY(B5)>5,S15,IF(S15>8,S15-8,0))


I AM NEEDING TO HAVE THE FOLLOWING:

IS A NUMBER IS PLACED INTO CELL P15, THE OVERTIME HOURS ARE NOT CALCULATED {CELL R15} ---ONLY IN THIS INSTANCE, OTHERWISE THE EXISTING FORMULA IS USED---
Something like this: =IF(WEEKDAY(B5)>5,S15,IF(S15>8,S15-8,0), If(P15>0) THEN do not calculate, IF(P15=0 or empty then [=IF(WEEKDAY(B5)>5,S15,IF(S15>8,S15-8,0))] is used.

 

[JDSOuth49]

Okay, the first image makes sense, but the second does not where row 49 shows 6, 14, 15, 15.
Travel Regular OT TOTAL
row 49 shows 6, 14, 1, 15.
What is the range F:O and how does it work?
CELL P15 = USED TO ENTER TRAVEL HOURS (CALCULATED AS STRAIGH TIME ONLY)
CELL Q15 = =IF(WEEKDAY(B4)<=5,IF(S15>8,8,S15),0)+IF(P15<>"",P15,0)

CELL R15 = CALCULATES OVERTIME (REFERENCES CELL S15 à ANY TIME OVER 8 HOURS IS PUT INTO CELL R15 [CELL R15 FORMULA= =IF(WEEKDAY(B5)>5,S15,IF(S15>8,S15-8,0)) --> =IF(P15>0,0,IF(WEEKDAY(B5)>5,S15,IF(S15>8,S15-8,0)))

CELL S15 = TOTAL of Q15:R15
Input range [Column 49F:49P]
=IF(P15>0,0,IF(WEEKDAY(B5)>5,S15,IF(S15>8,S15-8,0)))
Will there be more than 1 entry per row?
It is possible as the example suggests (It depends on how many cost codes the employee works on) and if there is travel involved

 

[dreid1011]

Does these results look like what you would expect for the inputs?

Book1
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
48	cost codes										travel	regular	ot	total
49				9							6	14	1	15
50				5							6	11	0	11
51				9							0	8	1	9
Sheet2

Cell Formulas
Range	Formula
Q49:Q51	Q49	=MIN(8,SUM(F49:O49))+P49
R49:R51	R49	=MIN(MAX(SUM(F49:O49)-8,0),SUM(F49:O49))
S49:S51	S49	=Q49+R49

 

[JDSOuth49]

Yes, pure genius. This is awesome and remarkably simple

Thank you so much.

 

[dreid1011]

Yes, pure genius. This is awesome and remarkably simple

Thank you so much.

You're welcome, and happy to help. Let us know if you need further assistance with it.

 

[JDSOuth49]

Thank you..

I am sorry to ask but
the 1st part of the formula still needs to be there:
=IF(WEEKDAY(B5)<=5,IF(S49>8,8,S49),0) =MIN(8,SUM(F49:O49))+P49

 

[JDSOuth49]

rather the =IF(WEEKDAY(B5)<=5

 

[dreid1011]

I am sorry to ask but
the 1st part of the formula still needs to be there:

rather the =IF(WEEKDAY(B5)<=5

I thought as much. Tell me what should happen if the weekday is <= 5 and if weekday is > 5?

 

[JDSOuth49]

Cell B5 = =WEEKDAY(C6,2)

MON=1
TUES=2
WED=3
THURS=4
FRI=5
SAT=6
SUN=7

Cell B5 returns the day of the week based on the date that is input into cell C6

When an employee works on a "Saturday or Sunday" the hours that they work are automatically calculated as overtime.

Example: Saturday 3/30/24, any hours input into (F49:O49) will be automatic OT. and returned in cell R49

 

[dreid1011]

Okay, how about this. Row 49 includes travel time as OT, row 50 does not include travel time as OT:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
47	3/30/2024
48					cost codes										travel	regular	ot	total
49								9							6	0	15	15	Include Travel Time in OT
50								5							6	6	5	11	Do Not Include Travel Time in OT
Sheet2

Cell Formulas
Range	Formula
Q49	Q49	=IF(WEEKDAY($B$47,2)<=5,MIN(8,SUM(F49:O49))+P49,0)
R49	R49	=IF(WEEKDAY($B$47,2)<=5,MIN(MAX(SUM(F49:O49)-8,0),SUM(F49:O49)),SUM(F49:O49)+P49)
S49:S50	S49	=Q49+R49
Q50	Q50	=IF(WEEKDAY($B$47,2)<=5,MIN(8,SUM(F50:O50))+P50,0+P50)
R50	R50	=IF(WEEKDAY($B$47,2)<=5,MIN(MAX(SUM(F50:O50)-8,0),SUM(F50:O50)),SUM(F50:O50))

 

[JDSOuth49]

dreid1011​

I like Q50 & R50 formulas:
From the original code (Column Q) =IF(WEEKDAY(B5)<=5,IF(S19>8,8,S19),0)
MON=1
TUES=2
WED=3
THURS=4
FRI=5

From the original code (Column R) =IF(WEEKDAY(B5)>5,S19,IF(S19>8,S19-8,0))
SAT=6
SUN=7

When I put the formulas into my spread sheet all is fine until i noticed that when the date is set to Saturday the hours are still being split between (Reg Time and OT)

this is true for Mon - Fri "<=5"

For Sat and Sun (all hrs should be OT) ">5"