Timesheet formula

[Meep]

I need assistance on a formula to calculate employee hours. I am using a template from a previous timesheet which rounded off minutes. So if the hours came to be 11:45, it would be rounded to 11:66.

The formula was as follows:

A5_________B5 _____________C5 ___________________D5
IN_________OUT __________ LUNCH_________________ TOTAL
8:00_______19:15 _________=MOD(B5-A5,1)-"1:00" _________=INT(C5*24)+LOOKUP(MINUTE(C5),{0,10,20,30,40,50},{0,0.25,0.33,0.5,0.66,0.75})

My question is how I can calculate the employee hours WITHOUT rounding the minutes.

If an employee works 11hrs 45mins, then I would like to keep the minutes as accurate as possible.

Of if someone would explain how this formula =(B5-A5+(B5<C5))*24 would give a value of 11:25 (if A5= 8:00 and B5= 19:15). Shouldn't the total value be 11hrs 15mins?

I would like to know how are the minutes calculated (i.e. how the 15min turns to :25), and what formula can I use that will accurately calculate the minutes so it does not get rounded improperly and to prevent the employee from getting overpaid or underpaid.

Simply stated, I just would like to put in the hours from our employee timecards, deduct one hour for lunch, and the total would be the total hours AFTER the one hour lunch deduction; which can be auto summed at the end of each week.

Thanks for any help.

Meep

[daddylonglegs]

Hello Meep

You can get the results in time format if you don't multiply by 24, i.e. use just

=B5-A5+(B5<C5)

Then the result for your example will be 11:15 (you have to format the cell with the formula as hh:mm)

but if you use the formula you suggested, i.e.

=(B5-A5+(B5<C5))*24

then format the result cell as number then you get the result in decimal format, i.e. 11.25 (because 15 minutes is quarter of an hour, i.e. 1/4 = 0.25)

Both results can be valid depending on your requirements

[Meep]

Thanks for your help.

My mind must be AWOL for me to forget 15/60 = 25.

Meep