Jan 03 2023 06:42 AM
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
Jan 03 2023 08:22 AM - edited Jan 03 2023 08:27 AM
Solutionyou 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
Jan 04 2023 04:14 AM
Jan 04 2023 04:55 AM
Jan 04 2023 08:40 AM
Jan 05 2023 07:16 AM
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.
Jan 05 2023 08:26 AM
Jan 09 2023 01:02 AM