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.
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.