Forum Discussion
Using a formula to determine the end time
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
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
7 Replies
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.
