Error in a If Round function.

[CrazyDave]

I have a spreadsheet, which, in part, records my start and finish times. From this, I work out the length of each shift. I have a formula to round the length of each shift to the nearest 15 minutes. That works. My issue is that the same formula also looks to see if the length of the shift is less than 2 hours.... if it is less than 2 hours, the formula is supposed to return a shift length of 2 hours... it doesn't... it rounds it out to the nearest 15 minutes... Quite frustrating really....
AL is the column for my Start Time.
AN is the column for my Finish Time.
AQ is the column where the formula gets entered. This is the formula - =IF(AN2="","",IF(AN2-AL2>"2:00","2:00",ROUND((AN2-AL2)*96,0)/96))

4:15 to 6:15 calculates as 2:00
3:15 to 5:54 calculates as 2:45

But... 4:15 to 6:04 calculates to 1:45, when it should be 2:00.

I've even tried to reverse this by switching the '>' to '<' and putting the result of "2:00" to the end of the formula, and I get the same results....

Any help will be much appreciated.

 

[KRice]

Give this a try:
=IF(AND(ISNUMBER(AN2),ISNUMBER(AL2)),MAX("2:00",ROUND((AN2-AL2)*96,0)/96),"")

 

[CrazyDave]

Many thanks KRice!!!
I think I'd need to do some research as to how this works...

 

[KRice]

Glad to help. Briefly, the formula confirms that the two time cells contain numbers, and if either is blank, then a blank is returned. If both numbers (times) are present, then the difference is taken and rounded to the nearest 15 minutes using your original formula. If the difference in times is less than 2 hours, you still want 2 hours returned. A convenient way to achieve that outcome is to use the MAX function, setting the lowest value to 2:00 and the other argument to the true time difference rounded to the nearest 15 minutes. So if the real time difference is greater than 2:00, you'll get that result; and if it is less than 2:00, the MAX function will ensure that 2:00 is still returned as the answer. Post back if you run into any difficulties.