help for a IF argument...

[Lisachocfrog]

I have a timesheet for which I need to set a condition, this is what i want...
j10 is equal to the time someone worked (in "hh:mm) and k10 is the hours they will be paid for (07:36in "hh:mm"). m10 will equal any acrrued time (time off in liue of overtime) over 8 hours worked. but as you cannot display a negative time if someone works less than the paid hours (k10), i need it to also say if j10-k10 is less than "0", time accrued is zero. the difference between the paid hours (07:36) and 8 hours is externally monitored so I just need to know what they work over 8 hours, not over k10.

Help please anyone....slow death by excel here.....

[:) Sixthsense :)]

You will get a quick response if you create and attach a sample workbook with your example data and the expected output by covering the all the logic.

[Lisachocfrog]

sorry have added attachment sample

[:) Sixthsense :)]

Try this in M10 cell

=IF(OR($J10="",$K10=""),TIME(0,0,0),IF($J10>$K10,$J10-$K10,TIME(0,0,0)))

refer the attachment for details.

Let us know if your requirement is not fulfilled.

[Lisachocfrog]

thanks, yes that works nicely, however, just to complicate matters in this timesheet the time to be paid is for 7 hrs 36mins, regardless of the hours worked. the "accrued TOIL" is only calculated after the total hours worked is over 8 hrs, so somehow I need to show this formula to calculate the difference between hours worked & to be paid minus 24 mins only when the total hours worked is over 8 hours, the way i am doing this the error says I have too many functions...so there is a bit of swearing....

[:) Sixthsense :)]

Try this in K10 cell

=IF(TIME(HOUR($J10),MINUTE($J10),SECOND($J10))>TIME(7,36,0),TIME(7,36,0),$J10)

Try this in M10 cell

=IF(TIME(HOUR($J10),MINUTE($J10),SECOND($J10))>TIME(8,0,0),$J10-TIME(8,0,0),TIME(0,0,0))

Refer the attached file for details.

Hope that helps!

[vlady]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as "SOLVED".
For the meantime I'll do it for you.

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.