Listing a series of times in time order

[Theale]

I work as an exam invigilator for International Baccalaureate exams.
These typically run from 60 minutes to 150.
The rules require the invigilator to announce when the candidates have 30 minutes and 5 minutes remaining.

The added complication is that some candidates with special needs are allowed either 10% more time or 25% more time (Just these extra times - no other percentages).

To cope with this I created the attached spreadsheet showing two exams side by side - we sometimes have two exams running at the same time but with different durations.

1) I would be grateful for a look at my spreadsheet to spot any improvements

2) How can I extract the times from the shaded areas and list them in time order, so as I go through the exam I can make the right announcements at the right time
e.g.
09:27 30 min Exam B regular
09:52 5 min Exam B regular
10:01 5 min Exam B 10%
10:03 30 min Exam A regular
10:06 Finish Exam B 10%
10:14 5 min Exam B 25%

etc

Thoughts and advice gratefully received - equally if it is of use to you, you are welcome to the file.

David

[hoyasaxa215]

I think the easiest way to do this is to create a separate table with two columns.

Column 1 - Description of applicable test/reminder (e.g., Exam A - 10% extra - Finish or Exam B - 25% - 5 mins left)
Column 2 - A link to the cell containing the time with ABSOLUTE REFERENCES (e.g., $C$11 or $F$14)

Then just sort the table by Column 2.

[Richard Buttrey]

See if the attached is of any use

In J8:L14 I've created a schedule of times that is automatically listed with the earliest finish times first. Each row in the table is hidden until the finish time is reached and then the conditional format will switch of the font colour of white which is effectively hiding the cells.

This table is based on the working area starting in column O which in turn is based on your layout.

The system includes a macro which updates the J6 Time every second and it's that time that is used in the conditional format to compare against the finish times in the table.

Click the Start Clock to start the timer and Stop Clock to stop it.

I copied your original Sheet1 in case I needed to get back to your original times. Obviously being based on real times you;ll need to test it by setting unrealistic durations and start times since you won't want to wait for an hour or more to confirm that it's working, so set some durations of a minute or so from a near current start time.

I've not tested it on a Mac so let me know if you encounter any problems

[shg]

Here's another way; it uses a macro. If the output format is useful, I'll post the workbook.

	A	B	C	D	E	F	G	H	I
1	Start time		8:33	8:33	8:33		8:45	8:45	8:45
2	Duration		2:00	2:00	2:00		1:30	1:30	1:30
3	Extra Time		0%	10%	25%		0%	10%	25%
4	Duration		2:00	2:12	2:30		1:30	1:39	1:52
5	End		10:33	10:45	11:03		10:15	10:24	10:37
6
7	Time		Event	Event	Event		Event	Event	Event
8	9:45						30 Min: 9:45
9	9:54							30 Min: 9:54
10	10:03		30 Min: 10:03
11	10:08								30 Min: 10:08
12	10:10						5 Min 10:10
13	10:15			30 Min: 10:15			Finish: 10:15
14	10:19							5 Min 10:19
15	10:24							Finish: 10:24
16	10:28		5 Min 10:28
17	10:33		Finish: 10:33		30 Min: 10:33				5 Min 10:33
18	10:38								Finish: 10:38
19	10:40			5 Min 10:40
20	10:45			Finish: 10:45
21	10:58				5 Min 10:58
22	11:03				Finish: 11:03

[Theale]

Hi "shg"

The macro format that you post is ideal as it makes the time at which something has to be done obvious.
Please do post the workbook.
Very many thanks from this only average Exel user.
David

[Theale]

Hi Richard

Only problem is that my MacBook running OS X El Capitan can't open the .xlsm file. Is it possible to have it as an xls file or alternatively what's your suggestion for opening it?
But in any case thanks a lot for your quick, helpful, response.
David

[Richard Buttrey]

...realised I'd missed out the 30 and 5 minute warnings. I've extended the table accordingly

[shg]

Here you go ...

[Richard Buttrey]

Hi,

Here's a .xls. I received a warning when saving that some formats are not consistent with .xls so it will save with the nearest available format. Not sure what they might be, maybe the two button shapes.

[shg]

As an xls file ...

[Theale]

Great
That is so helpful.
In fact the start times will be the same for "normal", "10%" and "25%: so I have made D2 and E2 equal C2, so the invigilator, if it is not me (and who may not be very computer literate) can simply enter the duration at C2.
Likewise the basic exam duration will be the same for all three categories, so I've done the same for D3 and E3.
This forum is great and I am very grateful to all those who have helped me. I wish I could pass it on, apart from "adding reputation" - which I have.
David

[Theale]

Thanks for taking the trouble.
Appreciated.

[shg]

You're welcome.

[Theale]

Hi
One small issue: When I change the times and then click "Align keys", nothing happens and the event times in column A do not change. Instead I get an error message "Personal.xls could not be found". I suspect this is the formula which re-calculates the values for column A?
What should I do to fix this?
Thanks.

[shg]

Right-click the button, Assign Macro, and select AlignKeys.

[Theale]

Apologies, but I still don't understand how to activate "Align keys" as per my message above.
I assume there is a macro that I should assign, but where do I get it from?

David ("Theale").

[shg]

The macro is in the workbook. When you click Assign Macro, it will appear in the macro dialog that pops up.