PDA

View Full Version : payroll weekly time excel formula



jb1598
08-11-2008, 08:27 PM
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
08-12-2008, 12:49 AM
=MOD(B4-B3,1)-(MOD(B4-B3,1)>--"06:00")/24

jb1598
08-12-2008, 09:39 PM
=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
08-12-2008, 10:24 PM
Go to format cells > number > custom > type > [h]:mm:ss

jb1598
08-13-2008, 09:03 AM
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
08-13-2008, 09:45 AM
=IF(B3="OFF",0,MOD(C3-B3,1)-(MOD(C3-B3,1)>--"06:00")/24)