Solving for OT after certain hours in a time-card

I've got a simple time-card with the following columns:

Time In (C) | Time Out (D) | Total Hours (E) | Overtime (F)

Right now, in column F(OT) I've got the formula of

Please Login or Register  to view this content.

so that the cell is empty before any information is put in to the adjacent cells, but also because a "normal" workday is from 07:30 to 15:30. However, OT doesn't truly kick in until you've worked 8 hours. So if someone clocks in and works from 08:00-16:00 my formula is obviously returning a 1/2 hour OT when, in fact, no overtime was worked.

I've tried changing the formula to

Please Login or Register  to view this content.

but that returns an incorrect result and doesn't really account for the Time In/Time Out anyway. I've also experimented with the D4-TIME() and D4-TIMEVALUE() functions (instead of D4-8) but that wasn't producing the results I wanted either.

I assume it will require an "AND" or "OR" operator, but the attempts I've made have been wildly inconsistent.

Please Login or Register  to view this content.

I know at this point that it's probably a simple answer but for some reason I just can't wrap my head around the order of operations or if I'm even on the right track. Would be grateful for any help.

Thanks in advance.

Originally Posted by FerdyHar

As we don't have a sample file, so I have to assume some things.

You state at the top that you have a Total Hours column, is this total hours of the day worked by that person or total hours worked by that person in a week/month/year?

If it is that day. you can easily use

Formula:

Please Login or Register  to view this content.

Ferdy

Thank you! I think I see my issue here. I wasn't thinking in terms of nested IF functions and I was looking at times as whole numbers rather than percentages of 24 hours (which is, I'm guessing, what the 1/3 represents?).

Regardless, your first formula works a charm. Many thanks for that.