Using IF function and calculation for time in hours and minutes

Question

We will be paying out any employees who have over 40 hours of vacation. I cannot seem to get the formula to work. My formula is: =IF(E2>F2,E2-F2,0). As you can see in the screen shot, it works most of the time but there are a few that show up as ####. All of the cells are formatted as [h]:mm. Any ideas?

Answer 1

What do you see if you select columns E and F, and set the number property to General?

Answer 2

Same result

Answer 3

Oh - I see now. The values are left-aligned while the Alignment group on the Home tab of the ribbon shows that no specific horizontal alignment has been set.

The values aren't time values but text values. When evaluating E2>F2, Excel compares the values as text. and since "5" comes after "4", "5:09" is seen as greater than "40:00".

Use

=MAX(TIMEVALUE(E3)-TIMEVALUE(F3),0)

Answer 4

Your logic about the values makes complete sense. I tried your formula but it seems to only work on some of the numbers. Is there a way to format the cells so Excel will evaluate E2>F2 as values and not text?

Answer 5

Try this in G2:

=MAX(1*E2-1*F2,0)

Another option is to convert columns E and F to real time values:

Select column E.

On the Data tab of the ribbon, click Text to Columns.

Without changing any settings, click Finish.

Do the same for column F.

You can then use =MAX(E2-F2,0)