Show 40 hours worked

[drdanconnie]

I'm summing total hours worked in cell J5, formatted as [h]:mm, to =sum(c5:I5) and getting 48:44. In cell K5. also formatted as [h]:mm, using formula =IF(J5>=40,40,J5) , I'm seeing 48:44 when I expect to see 40:00. In the next column, L5, formatted as [h]:mm using formula =(J5-K5), I would like to see the over time hours of 8:44, but instead I'm seeing 0:00. Finally I would like to see the total pay using formula =(K5*B2)+(L5*B2*1.5) where B2 is $11.00, the pay per hour formatted as currency 2, which shows $.33. Tall order...please advise. Thank you.

 

[Aryatect]

Hi, Welcome to the message board, your formula should check =(J5>=40/24,40/24,J5) as 40 hours are 1.66666 in numeric value. If you are looking for further assistance can you please post some sample data and expected results, that will help in getting to a solution.

 

[Mentor82]

Hi,
How to you store the time? Do you use standard excel for displaying time? If so please note that although you have the cell formatted to display hh:mm, excel stores time value as a float no where 1 hour is 0.041(6) - it comes from 1(day)/24hours but one minute is 1/(24*60). So in that case 40hrs will be stores by excel as 1.(6). Format the cell to general where you have summed timethe 48:44 hrs and let me know what value you excel displays-is it 2,030555...?
Regards,
Sebastian

 

[Joe4]

Note:

A simpler way of writing:

[COLOR=#333333] =(J5>=40/24,40/24,J5)[/COLOR]

is to just use the MIN function:

=MIN(J5,40/24)

which basically says to take the lesser of the two values.

 

[drdanconnie]

Thank you to all for the suggestions. The spreadsheet calculations now work correctly. Again, thank you for all your help.

 

[Aryatect]

Glad we could help and thanks for the feedback!