Using a formula to determine the end time

[AC2023]

Hello,

We use a formula in excel to determine what time an exam should finish following reasonable adjustments awarded to disabled students, for instance, where extra time and / or rest breaks have been awarded. We currently use the below formula:

=CEILING(N2+TIME(0,(VALUE(IF(J2="",(0),(VALUE(J2))))+VALUE(IF(K2="",(0),(VALUE(K2)))))*((N2-M2)*24),0),"00:05")

The formula only works where:

Column N contains the student's standard exam end time (before any adjustments)

Column J contains the student's extra time allowance (for example, 15 minutes per hour)

Column K contains the student's break time allowance (for example, 10 minutes per hour)

Column M contains the student's start time

The formula normally rounds up their 'new' end time to the nearest 5 minutes and this is generated in Column O, an example is provided below (dummy data):

USN

First Name

Last Name

College

Subject

Paper code

Paper title

Venue

Long Descr

Extra Time

Break Time

Exam Date

Start Time

End Time

New End Time (column O)

1

Benjamin

Blue

LO

ENG

45

Eng paper 1

Home

Needs specific seating

15

05/01/2023

10:00:00

11:30:00

11:55:00

2

Josh

Green

LO

ENG

46

Eng paper 2

Home

None

10

05/01/2023

10:00:00

12:00:00

12:20:00

3

Tom

Brown

MO

MAT

62

Mat paper

Away

Needs specific seating

15

06/01/2023

10:00:00

11:30:00

11:55:00

Whilst the formula has worked correctly above, it doesn't always work and this leads to errors. Moreover, moving forward, we would like the formula to calculate the exact end time, rather than round up the time to the nearest 5 minutes.

Please could anyone help with this and advise what formula would calculate the new exact end time?

A sincere thank you in advance to anyone that can support. 

Best wishes

A 

 

[#2 · Accepted solution]

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

[#3]

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

[#4]

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.

[#5]

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.

[#6]

@mtarler 

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.  

[#7]

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.

[#8]

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!