Help with Inbedded If statement

S

sonar

Hi,

I have the following:

CD18 = text column
CB18 = my overtime
CC18 = my night time
(IF((CD18)<>"",0,(IF(OR(BW18=0,BX18=0),0,(IF(BW18<TIME(7,0,0),TIME(7,0,0)-BW18,0))+(IF(BX18>TIME(19,0,0),BX18-TIME(19,0,0),0))))))
]

My scenario:

Person works 8hrs per day. When he works 10hrs, 2 hrs are cal. a
overtime. But if the 2hrs falls after 7pm or before 7pm, it also form
part of the night time calculation. however, in this situation if it i
both overtime and night time rate, overtime rate takes precedence eg
1.5, and not 1.10.

Need to do the following:

I need my CC18 column to look at my CB18 column and assess if there i
overtime, then to assess if its more or less than my night hour
calculated in column (CC18), after that - deduct the lesser of the tw
from the greater of the two.

Why I need help:

I have tried this, but I am not sure if it refuses to work due t
looping ( or reference to same cell that is), or something I am doin
wrong.

'www.epping.co.za/register.xls' (http://www.epping.co.za/register.xls


=IF((CD18)<>"",0,(IF(CB18>(IF(OR(BW19=0,BX19=0),0,(IF(BW19<TIME(7,0,0),TIME(7,0,0)-BW19,0))+(IF(BX19>TIME(19,0,0),BX19-TIME(19,0,0),0)))),(CB18-(IF(OR(BW19=0,BX19=0),0,(IF(BW19<TIME(7,0,0),TIME(7,0,0)-BW19,0))+(IF(BX19>TIME(19,0,0),BX19-TIME(19,0,0),0))))),((IF(OR(BW19=0,BX19=0),0,(IF(BW19<TIME(7,0,0),TIME(7,0,0)-BW19,0))+(IF(BX19>TIME(19,0,0),BX19-TIME(19,0,0),0))))-CB18))))

Please hel
 
B

Biff

Hi sonar!

I downloaded your file. Let me see if I figure out what it
is your trying to do. Give me a day or two.

Biff
-----Original Message-----
Hi,

I have the following:

CD18 = text column
CB18 = my overtime
CC18 = my night time [
(IF((CD18)<>"",0,(IF(OR(BW18=0,BX18=0),0,(IF(BW18<TIME (7,0,0),TIME(7,0,0)-BW18,0))+(IF(BX18>TIME(19,0,0),BX18-
TIME(19,0,0),0)))))))
]

My scenario:

Person works 8hrs per day. When he works 10hrs, 2 hrs are cal. as
overtime. But if the 2hrs falls after 7pm or before 7pm, it also forms
part of the night time calculation. however, in this situation if it is
both overtime and night time rate, overtime rate takes precedence eg.
1.5, and not 1.10.

Need to do the following:

I need my CC18 column to look at my CB18 column and assess if there is
overtime, then to assess if its more or less than my night hours
calculated in column (CC18), after that - deduct the lesser of the two
from the greater of the two.

Why I need help:

I have tried this, but I am not sure if it refuses to work due to
looping ( or reference to same cell that is), or something I am doing
wrong.

'www.epping.co.za/register.xls' (http://www.epping.co.za/register.xls)
=IF((CD18)<>"",0,(IF(CB18>(IF(OR(BW19=0,BX19=0),0,(IF
Click to expand...
(BW19<TIME(7,0,0),TIME(7,0,0)-BW19,0))+(IF(BX19>TIME
(19,0,0),BX19-TIME(19,0,0),0)))),(CB18-(IF(OR
(BW19=0,BX19=0),0,(IF(BW19<TIME(7,0,0),TIME(7,0,0)-BW19,0))
+(IF(BX19>TIME(19,0,0),BX19-TIME(19,0,0),0))))),((IF(OR
(BW19=0,BX19=0),0,(IF(BW19<TIME(7,0,0),TIME(7,0,0)-BW19,0))
+(IF(BX19>TIME(19,0,0),BX19-TIME(19,0,0),0))))-CB18))))

Please help
Click to expand...
 
B

Biff

Hi sonar!

"But if the 2hrs falls after 7pm or before 7pm, it also
forms part of the night time calculation."

What exactly does that mean?

When do night hours start? (what time)

Biff
-----Original Message-----
Hi sonar!

I downloaded your file. Let me see if I figure out what it
is your trying to do. Give me a day or two.

Biff
-----Original Message-----
Hi,

I have the following:

CD18 = text column
CB18 = my overtime
CC18 = my night time [
(IF((CD18)<>"",0,(IF(OR(BW18=0,BX18=0),0,(IF(BW18<TIME (7,0,0),TIME(7,0,0)-BW18,0))+(IF(BX18>TIME(19,0,0),BX18-
TIME(19,0,0),0)))))))
]

My scenario:

Person works 8hrs per day. When he works 10hrs, 2 hrs are cal. as
overtime. But if the 2hrs falls after 7pm or before 7pm, it also forms
part of the night time calculation. however, in this situation if it is
both overtime and night time rate, overtime rate takes precedence eg.
1.5, and not 1.10.

Need to do the following:

I need my CC18 column to look at my CB18 column and assess if there is
overtime, then to assess if its more or less than my night hours
calculated in column (CC18), after that - deduct the lesser of the two
from the greater of the two.

Why I need help:

I have tried this, but I am not sure if it refuses to work due to
looping ( or reference to same cell that is), or something I am doing
wrong.

'www.epping.co.za/register.xls' (http://www.epping.co.za/register.xls)
=IF((CD18)<>"",0,(IF(CB18>(IF(OR(BW19=0,BX19=0),0,(IF
Click to expand...
(BW19<TIME(7,0,0),TIME(7,0,0)-BW19,0))+(IF(BX19>TIME
(19,0,0),BX19-TIME(19,0,0),0)))),(CB18-(IF(OR
(BW19=0,BX19=0),0,(IF(BW19<TIME(7,0,0),TIME(7,0,0)- BW19,0))
+(IF(BX19>TIME(19,0,0),BX19-TIME(19,0,0),0))))),((IF(OR
(BW19=0,BX19=0),0,(IF(BW19<TIME(7,0,0),TIME(7,0,0)- BW19,0))
+(IF(BX19>TIME(19,0,0),BX19-TIME(19,0,0),0))))-CB18))))

Please help
Click to expand...
.
Click to expand...
 
S

sonar

Thanks Biff

Its much appreciated, as this thing has been a headache from the start
and I have not been able to do these things without you guys help.

regards
Sona
 
B

Biff

Hi sonar!

Did you not see my other post?

I need you to explain this:

"But if the 2hrs falls after 7pm or before 7pm, it also
forms part of the night time calculation."

What exactly does that mean?

When do night hours start? (what time)

Is that just a typo? Should it say: "after 7pm or before
7am" ?

Your formulas are overly complex. Why not just subtract
the OT hrs (if any) from the NT hrs (if Any)?

Biff
 
S

sonar

Hi Biff, I tried subtracting the one from the other, but its not tha
simple.

=IF((CD14)<>"",0,(IF(OR(BW14=0,BX14=0),0,(IF(BW14<TIME(7,0,0),TIME(7,0,0)-BW14,0))+(IF(BX14>TIME(19,0,0),BX14-TIME(19,0,0),0))-CB14)))

it only works if the night hours is more than the overtime. an
unfortunately with an 8 hour person the overtime can occur anytime.

But if it occurs where the overtime is more than the night time, th
formula freaks out, gives me the #####'s. eg. person works 10hrs, 2hr
is overtime, but only 1hr from the 2hrs falls in the night tim
bracket. It wants to deduct 2hrs from 1hr and it cant do that.

I tried this,

=IF((CD18)<>"",0,
(IF(CB18>(IF(OR(BW19=0,BX19=0),0,(IF(BW19<TIME(7,0,0),TIME(7,0,0)-BW19,0))+(IF(BX19>TIME(19,0,0),BX19-TIME(19,0,0),0)))),

(CB18-(IF(OR(BW19=0,BX19=0),0,(IF(BW19<TIME(7,0,0),TIME(7,0,0)-BW19,0))+(IF(BX19>TIME(19,0,0),BX19-TIME(19,0,0),0))))),

((IF(OR(BW19=0,BX19=0),0,(IF(BW19<TIME(7,0,0),TIME(7,0,0)-BW19,0))+(IF(BX19>TIME(19,0,0),BX19-TIME(19,0,0),0))))-CB18))))

But it does not want to work. hence my problem.

So how do I fix this, as a person can not get night time allowance o
10% and overtime for the same hour worked, the greater one, which wil
always be overtime, will take presidence to the night hours.

:
 
S

sonar

Hi Biff

I found the solution to my problem, I had a little help with it fro
someone to make it work perfectly.

=IF((CD17)<>"",0,(IF(CB17>(IF(OR(BW17=0,BX17=0),0,(IF(BW17<TIME(7,0,0),TIME(7,0,0)-BW17,0))+(IF(BX17>TIME(19,0,0),BX17-TIME(19,0,0),0)))),0,(IF(OR(BW17=0,BX17=0),0,IF(BW17<TIME(7,0,0),TIME(7,0,0)-BW17,0))+(IF(BX17>TIME(19,0,0),BX17-TIME(19,0,0),0)))-CB17)))

regards
Sona
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If statement with time values 2
IF statements help 1
Help With If Statement 4
Sum product problem!! Help!! 10
Help with a nested IF statement 6
IF Statement Help 2
If statement help 3
I need help with the IF Statement 5

Top