Need help using time values in IF functions.

[jjkkss]

My spreadsheet is calculating my weekly time sheet.

DATE	START	END	HOURS	OT
MON	9:00 AM	5:00 PM	8:00	NO OT
TUE	9:00 AM	5:30 PM	8:30	0:30
WED	8:30 AM	5:30 PM	9:00	1:00
THU	9:00 AM	5:00 PM	8:00	NO OT
FRI	8:30 AM	5:30 PM	9:00	1:00
		TOTAL:	42:30	2:30

Right now, I am manually entering my START and END times in h:mm format into columns B and C. Column D is calculated using a SUM formula. Cell D7 and E7 are in [h]:mm format calculated using the formulas =SUM(D1:D6) and =SUM(E1:E6) respectively. What I want to do is calculate column E, my overtime. Right Now I enter my overtime manually, but I want to use a formula to calculate any time I work over 8 hrs per day. I want to use an IF function where the logical_test is (D1>8:00), the value_if_true is (D1-8:00), and the value_if_false is "NO OT". I tried the following:

=IF(D1>8:00, [D1-8:00], ["NO OT"])
=IF([D1>8], [D1-8], ["NO OT"])
=IF([D1>TIME{8, 0, 0}], [=D1-TIME{8, 0, 0}], ["NOT OT"])

(and several more variations of these)

I'm having two problems: first off, Excel is not allowing me to use a formula in the value_if_true space. Problem two, Excel is not recognizing using time values in formulas (ie. D1-8:00 meaning D1 minus 8 hours.)

Is it possible to using time values in formulas, and is it possible for one of the values in an IF function to be an additional formula?

[ConneXionLost]

Remember, Excel uses a decimal value for time, for example 8 hours would be 8/24 or 0.33333, so try:

Please Login or Register  to view this content.

Cheers,