payroll weekly time excel formula

[jb1598]

hello,

I am frustrated with this simple weekly payroll formula as the final results continue to be the same (-9). I am also getting a curcular error. My start time would be 9:00 am and my finish time would be 6:15 pm. If total time is greater than 6 hours, one hour would be deducted from the total time. This is my formula =IF((B3<B4),((B3-B4)*24),"IF((C4=>6-1)"). Can anyone please help me with this.

Thanks in advance.

[Bob Phillips]

=MOD(B4-B3,1)-(MOD(B4-B3,1)>--"06:00")/24

[jb1598]

=MOD(B4-B3,1)-(MOD(B4-B3,1)>--"06:00")/24

Thank you for your response and formula, but when I type this formula in with 7:00 AM (in B3) and 4:00 PM (in B4). I formated each cell as (TIME 1:30 PM)

The results that I get is 0.3. What the results I should get is 8. (9 total minus the one hour for lunch.

I played with the formula and cannot get the results to be the correct total time between the two, minus one hour for lunch.

Could you please advise me what I need to change or do to get this correct. Thanks in Advance.

[david000]

Go to format cells > number > custom > type > [h]:mm:ss

[jb1598]

Go to format cells > number > custom > type > [h]:mm:ss

=MOD(C3-B3,1)-(MOD(C3-B3,1)>--"06:00")/24

With this formula I tried to add a IF statement with the word "OFF", in cell B3 and have no hours entered in C3, and have the total cell (C4) = 0 hours worked for that one day, During the week one of my staff will have a day off for working on Sat. I have gotten a number of errors when I tried to add a IF statement to my exciting formula,

I am going brain dead here.

Thanks in advance.

[Bob Phillips]

=IF(B3="OFF",0,MOD(C3-B3,1)-(MOD(C3-B3,1)>--"06:00")/24)