If then statement and time format

[DonElston]

Trying to calculate Overtime using IF THEN. All cells are formatted for time.

If E22 is greater than 40 hours, I want 40, else E22. E22 is 51:00:00 and
the statement keeps comming back with the full 51:00:00.

Excel 2007.

Thanks in advance, I know it is something simple but after 45 minutes, I'm
fed up

Don

 

[bpeltzer]

It would help if you posted the formula you're using, but I'll guess that
you're comparing to the number 40. If you've got everything in time format,
you probably want to compare to 40/24 instead (40 would be 40 days; 40/24
makes it 40 hours).

 

[Toppers]

Try:

=IF(E22*24>40,40,E22*24)

Formatted as NUMBER

 

[DonElston]

Thanks

I still got bizzzre results, BUT rather than using the E22 result, I redid
the cell sums and with the 40/24, got the correct result.

=IF(SUM(H5:H11)>40/24,SUM(H5:H11)-40/24,0) Figures OT
=IF(SUM(H6:H12)>40/24,40/24,SUM(H6:H12)) Regular

Thanks again