Calculating regular time, subtracting any personal time

[Andrea]

I am trying to calculate my cell labeled regular time to subtract any
personal time taken and give me a subtotal (also without overtime hours). If
a person was to take a week of vacation, I would like for the cell to read 0
regular time.
For example, this is what I would want the regular time cell to read in each
situation:
Total hours: 40
sick hours: 8
regular time: 32
also
total hours: 50
sick hours: 8
regular time: 40
overtime: 2
also
total hours: 0
vacation: 40
regular time: 0
What formula would I use in regular time cell in order to calculate the
correct hours for each situation? Any help would be greatly appreciated!

 

[Luke M]

Assuming total time is in A2, personal time in A3

=IF(A2=0,0,MIN(A2-A3,40))

 

[Andrea]

Thanks Luke, this formula worked. After playing around with this formula for
different situations I came across one more exception. What if the total
time in A2 is 8 hours and personal time in A3 is 32, when I plugged your
formula in the result came out as <24>, which I would like for it to say 8.
Do you have any suggestions?

Thanks for all your help!

 

[Luke M]

You need to standardize how you want things to work first. In some of your
examples, total time = regular time + personal time (exception is overtime)
sometimes, total time = regular time regardless of what personal time is
(excpetion being no regular time)

In the example you just gave, why is personal time not counted? You need to
decide whether "total hours" will mean total hours of stuff done in the week,
or just work done (which is what you were originally doing with regular
time). Dy definition, total time should NOT be less than personal time +
regular time.

When you decide what the rules will be regarding whether or not to subtract
personal time from the total time, I'll/we'll be able to better provide a
solution.

 

[Andrea]

I'm sorry for not being very clear. This is my bad. Total time = actual
time worked + personal time. Regular time = actual time worked (but only up
to 40 hours, anything over would be reclassified as overtime). So, I want my
cell for regular time to calculate actual time worked from the total time,
but also remove overtime hours.

All help is much appreciated. Thanks again!

 

[Jake]

I'm also loooking for this, what I've got is =sum(J7:J11) and if it equals
more than 8 hours it needs to transfer over to K13. Sorry for hijacking yours
but thanks for the help. Jake