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]

I have tried something like this:

=IF(CELL(P15>0)=,0,IF(CELL(P15>0)=(WEEKDAY(B5)>5,S15,IF(S15>8,S15-8,0)))

If cell p15 contains a value DO NOT use the formula: IF(WEEKDAY(B5)>5,S15,IF(S15>8,S15-8,0)))

If cell p15 does not contain a value USE the formula: IF(WEEKDAY(B5)>5,S15,IF(S15>8,S15-8,0)))

 

[dreid1011]

It may be these unnecessary bits:

=IF(CELL(P15>0)=,0,IF(CELL(P15>0)=(WEEKDAY(B5)>5,S15,IF(S15>8,S15-8,0)))

Try this:

=IF(P15>0,0,IF(WEEKDAY(B5)>5,S15,IF(S15>8,S15-8,0)))

 

[JDSOuth49]

dreid1011,
=IF(P15>0,0,IF(WEEKDAY(B5)>5,S15,IF(S15>8,S15-8,0))) -- WORKED GREAT!!

NOW THERE IS AN ISSUE WITH THE FORMULA IN CELL (Q15)
=IF(WEEKDAY(B4)<=5,IF(S15>8,8,S15),0)+IF(P15<>"",P15,0) __Is this formula backwards as well?
I thought this formula was working correctly, but I guess I was wrong.

When =IF(WEEKDAY(B4)<=5,IF(S15>8,8,S15),0)+IF(P15<>"",P15,0), is used it is supposed to only calculate the regular hours for cell range (F15:O15) and then ADD cell P15.

But: it appears that it is actually add the value of P15,P16,P17, etc... + 8.

Any Ideas...

Thank you in Advance.

 

[dreid1011]

But: it appears that it is actually add the value of P15,P16,P17, etc... + 8.

Well yes, by the logic of the formula, it is adding 8 or the value of S15 (whichever is smaller) to P15 if P15 is greater than 0.

How should it be working?

 

[JDSOuth49]

=IF(WEEKDAY(B4)<=5,IF(S15>8,8,S15),0)+IF(P15<>"",P15,0)

CELL Q15 = (Captures the Regular time hours)
The original formula [=IF(WEEKDAY(B4)<=5,IF(S15>8,8,S15),0)] works as intended. Which is any value up to 7 equals that value, for 8 and above it only calculated 8 hrs for regular time and 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))

 

[JDSOuth49]

Sorry I sent it incomplete:
I needed to add [+IF(P15<>"",P15,0)] for cell p15. (but the value in P15 is only to be calculated as regular hours, not overtime]

 

[dreid1011]

=IF(WEEKDAY(B4)<=5,IF(S15>8,8,S15),0)

So, if this formula works as intended, when and why do you need to add P15 to it?

Sorry I sent it incomplete:
I needed to add [+IF(P15<>"",P15,0)] for cell p15. (but the value in P15 is only to be calculated as regular hours, not overtime]

What do you mean by calculating P15 as regular hours and not overtime?

 

[JDSOuth49]

What do you mean by calculating P15 as regular hours and not overtime?
Time Entered in "P15" is for Travel Time. All of our travel time must be at the regular pay rate.

So, if this formula works as intended, when and why do you need to add P15 to it?

Example: An employee worked (9 hours = 8 Hours of Regular Pay, and 1 Hour of Over time Pay), but the employee also had to travel to another Job site (A 6 hour Trip -- Entered in Cell P15).
Since the employee has already worked 9 hours (8 Hours of Regular Pay, and 1 Hour of Over time Pay) (Normally any extra time add would go directly to overtime, but travel time is a special case) and Time Entered in "P15" is for Travel Time. The 6 hours must be paid at the regular pay rate.

 

[dreid1011]

Okay, the first image makes sense, but the second does not where row 49 shows 6, 14, 15, 15.

What is the range F:O and how does it work? Will there be more than 1 entry per row?