Forum Discussion
Using a formula to determine the end time
you say it doesn't work sometimes and it might help to have examples when it doesn't work
I have reduced the formula a bit and removed the round-off. Hopefully this will work for you:
=N2+(IF(J2="",0,VALUE(J2))+IF(K2="",0,VALUE(K2)))*(N2-M2)/60
you say it doesn't work sometimes and it might help to have examples when it doesn't work
I have reduced the formula a bit and removed the round-off. Hopefully this will work for you:
=N2+(IF(J2="",0,VALUE(J2))+IF(K2="",0,VALUE(K2)))*(N2-M2)/60
- Thank you so much for your response and this adjusted formula, mtarler.
An example of when the original formula didn't work was in some cases where two students were sitting the same exam and had the same amount of extra time and / or rest breaks, the end time would calculate differently. For instance, the formula would calculate one end time correctly but then the end time for the other student student would be rounded up to the next 5 minutes, (although the students had exactly the same start and finish time and adjustments).- glad it helps. I would be curious to see an example when it didn't work. There is always a reason even if that is round off errors.
Hi mtarler. An example has happened when the original formula =CEILING(N2+TIME(0,(VALUE(IF(J2="",(0),(VALUE(J2))))+VALUE(IF(K2="",(0),(VALUE(K2)))))*((N2-M2)*24),0),"00:05")
has calculated the end time incorrectly. Hopefully you can see excel example attached.
Row 2, for example, the student is sitting a 1-hour exam and should have 15 minutes extra time and therefore the end time should be 10:15:00 but the end time has been calculated as 10:20:00. Having looked into this further, the formula always adds on an additional 5 minutes when the exam is 1 hour in duration but I'm not sure why. The same also happens in some instances when the exam is 2 hours in duration but not all the time (row 15, the new time is calculated incorrectly but in row 16, the end time has been calculated correctly). There is no issue when the exam is 3 hours long or more in duration.
I've also noticed that the calculated end time is sometimes wrong when the student has no extra time or break time allowance, but the calculated end time results in there being 5 minutes more than the standard end time (example in row 12 and 14 but row 13 is correct).
If you could shed any light on this, that would be really appreciated again. Having discussed this with colleagues, it may be that we do want to use a formula where it does round up the 'new' time to the nearest 5 minutes so any advice would be welcomed.
