Time Sheet - Overtime

[smcrum]

I am trying to get a time card formatted correctly where the overtime is calculated as well, but am having difficulty getting this accomplished. If the employee works over 40 hours in a week then the overtime is calculated. How would I get the overflow of hours to go into a different cell?

Thank you!

[teylyn]

Hi smcrum

welcome to the forum,

maybe something like

=if(a1>40,a1-40,0)

to calculate overtime with total time worked in A1, but...

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[JBeaucaire]

You can sum a row of cell values with daily hours like so in A8:

=SUM(A1:A7)

Now, to get that formula to stop at 40 hours, use this in A8:

=MIN(40, SUM(A1:A7))

...and to see the overtime in A9:

=MAX(0, SUM(A1:A7)-40)

[smcrum]

Thank you JBeaucaire that did exactly what I was looking for!

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)