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
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).
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.
- I should have noticed this before. You are using CEILING( .... ) which means ANY round off error on the high side causes it to round up. Basically you are taking (StartTIME - EndTIME)*24 and each of those times are trying to represent fractions of a day (i.e. hours and mins) but 1/24 is a repeating number that has to get truncated/rounded off at some point not to mention represented in binary logic which has some resolution. I won't and can't go into all those details but basically 1 hour difference result in 1.00000012313 so carry that insignificant error through and then take CEILING and it will ROUND UP to the next 0:05 minutes accordingly. Actually looking at this some more, even if the 15 mins is 'corrected' for, the result of TIME(0,15,0) is 0.0104166666666667 and notice the rounded up 7 at the end so CEILING will also 'round up' for that.
SO easiest solution is change CEILING to MROUND
IF you want to force CEILING for any increase that isn't 'insignificant' then maybe:
=CEILING(MROUND(N2+TIME(0,(VALUE(IF(J2="",(0),(VALUE(J2))))+VALUE(IF(K2="",(0),(VALUE(K2)))))*((N2-M2)*24),0),"00:00:01"),"00:05")
so basically first round off to nearest second and THEN round UP
of course you could choose nearest 2 min if you want to ignore anything <1min.
and of course you can apply it to the simplified formula I gave above:
=CEILING(MROUND(N2+(IF(J2="",0,VALUE(J2))+IF(K2="",0,VALUE(K2)))*(N2-M2)/60,"00:00:01"),"00:05")
because although it is BETTER than the original since it isn't converting from days->hours->mins->days it will still exhibit some round-off errors not noticeable when you are just looking at the displayed time value but if you apply CEILING() you may see.- Thank you - this formula is working! 🙂 I will have a play some more and hopefully won't get myself in a muddle. Thank you so much again!
- Due to GDPR, we no longer have that data so unfortunately I can't share an example but I'm sure there was a reason for it. If anything similar comes up again, I will share so you can see.
You have been so helpful - thank you again.