I realize that if I just put the hours worked myself in the
spreadsheet I just created this week, i.e., did the calculation myself
and just typed in the actual hours worked, that that would avoid the
problem I'm running into. However, it would be just so much easier
and so neat to type in the actual start and end times involved and to
then let Excel figure out what the overtime is if there is indeed
overtime <g. That would be too kewl for words! <g

I did up an overtime logger/calculation spreadsheet. It worked fine
as is until today; today I didn't work a full day so the calculations
were useless for this date.

I yielded results on a massive search today on the net. The formula
below is what I was able to come up with and I have this in cell E2
which subtracts 7.5 hours from D2 (corresponding to regular hours
worked so must not be included in overtime calculation):

=IF(D2<"",SUM(D2-TIME(7,30,0)),"")

D2 shows 7h30m (calculated by end time C2 - start time B2 less 30
minutes for lunch) and E2 shows 0h0m because the above formula
subtracts 7 hours, 30 minutes to account for regular work hours vs
overtime, as mentioned above.

Up till here, so far so good.

If I do actually work 7.5 hours, then, I get this 0h0m display.
But if I didn't work 7.5 hours, like today, the above doesn't work.

I had an appointment today and so I actually only worked 4.5 hours.
D5, then, shows 4h33m instead of 7h30m.
But E5 shows #### where it should show -2h57m
(because I didn't work nearly 3 hours due to my appointment).

I've tried displaying the end results differently through cell
formatting, but nothing works.

The goal is to just put the time started in the morning and then my
usual time out and then to put the time I left. So by entering 3
different times only, and without taking out a calculator, when I left
at 6:30 p.m. tonight, the spreadsheet should determine that on this
day I didn't work overtime and that I am still actually 1 hour short
or so.

I know this is tough to understand by description, but hope the
concept itself is understood even if not the above.

So, here is the problem again in a nutshell:

How to get the above #### to display the time correctly when there is
a negative number of hours to show, i.e., in this case -2h57m
yet keep 0h0m when I did work the full day.




(I use custom time display of: h"h"m"m" to get our French Canadian
24 hours display which is easiest for all to understand as that's what
we use in our dept.)

Thanks in advance!