Error in a If Round function.

CrazyDave

New Member
Joined
Jan 15, 2014
Messages
33
Office Version
  1. 2013
Platform
  1. Windows
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.
Capture.JPG
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
Give this a try:
=IF(AND(ISNUMBER(AN2),ISNUMBER(AL2)),MAX("2:00",ROUND((AN2-AL2)*96,0)/96),"")
 

CrazyDave

New Member
Joined
Jan 15, 2014
Messages
33
Office Version
  1. 2013
Platform
  1. Windows
Many thanks KRice!!!
I think I'd need to do some research as to how this works...
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,996
Messages
5,543,188
Members
410,584
Latest member
Bluefox68
Top