[Solved] Time sheet formula

[Daddymae]

=IF(S8>0;T8-S8)
I'm using this formula in a time sheet and it gives me total hours worked per day however, I then need it to deduct 1 hour but only if the total hours worked are more than 6.

Can anyone furnish me with a solution please as I've spent many days trying to solve it. It seems to be a simple equation but I'm a new user and I just can't get it?
Any help would be greatly appreciated.

Thank you!

[Villeroy]

T8-S8 returns the difference.
=MIN(T8-S8 ; 1/24) returns either the difference or 1 hour depending on which one is smaller.

[Daddymae]

Thank you for your quick reply Villeroy

Although it doesn't solve my problem. If an employee works for 5 hours that's what he is paid. If he works for 8 hours, 1 hour is deducted for lunch break and he is paid for 7 hours. What I need the formula to do is if total hours worked exceed 6 hours then 1 hour needs to be deducted. Example: if 4 hours worked then 4 hours paid but if 7 hours worked = 6 hours paid, if 8 hours worked = 7 hours paid and so on.

Your formula works but it doesn't go past the parameters that are set within it, I need it to deduct 1 hour from the total hours worked over 6 hours if that makes sense?

[RusselB]

If your times are true times, which for Calc means portions of a day, then I think you'd want

Code: Select all

=T8-S8-if(T8-S8>1/4;1/24;0)

On the other hand, if your times are not being entered as portions of a day, then you probably want

Code: Select all

=T8-S8-if(T8-S8>6;1;0)

Try both codes to see which one works for you. If, by some chance, neither one works, then please attach an example of your spreadsheet so that we can see just how your numbers are being handled.

[Daddymae]

Cracked it RussellB, thank you!

It was your true times formula that worked .

You have my most sincere gratitude!
Kindest regards