Scheduling Program - Finding Open Slots

[dondoggy]

I need to find the available times each day when a building/facility is not currently being utilized. I have been able to build a PivotTable which shows use for each day and times, and allows me to see when facilities are open. However, making a list of the available times for each facility (n=200+) for each day of the week is proving a major problem. Any ideas?

Column A is Building Number Column B is the Facility ID Column C is the Days Used (e.g. M,MW,MTWR,R, F, W,etc.) Column D is the Start Time of usage Column E is the End of Time of said usage.

Sample data below: Building Facil ID DaysUsed Start time End time B3 A3 M 5:00 PM 10:45 PM B3 A8 MW 12:30 PM 1:45 PM B1 A10 MW 8:00 AM 9:15 AM B2 A36 MW 5:30 PM 6:45 PM B1 A39 MTWR 8:30 AM 10:30 AM B3 A42 MW 9:00 AM 11:20 AM B2 A8 MW 9:30 AM 10:45 AM B1 A30 MW 9:30 AM 10:45 AM B2 A34 MW 11:00 AM 12:15 PM B2 A41 M 11:00 AM 12:15 PM B2 A47 MW 12:30 PM 1:45 PM B2 A53 MW 9:00 AM 9:50 AM B3 A8 M 7:00 PM 9:50 PM B3 A31 M 9:30 AM 10:45 AM B2 A44 MW 4:00 PM 5:15 PM B1 A46 MW 11:00 AM 12:15 PM B3 A54 MW 5:30 PM 6:45 PM B3 A12 M 12:30 PM 1:45 PM B3 A22 MW 12:30 PM 1:45 PM B2 A38 MW 9:30 AM 10:45 AM B1 A49 MW 5:30 PM 6:45 PM B1 A49 M 9:00 AM 10:40 AM B3 A53 M 7:00 PM 8:40 PM B1 A9 MW 9:30 AM 10:45 AM B2 A14 MW 12:30 PM 2:50 PM B3 A22 MW 2:00 PM 3:15 PM B3 A38 M 7:00 PM 9:50 PM B1 A55 MW 11:00 AM 12:15 PM B3 A58 MW 12:30 PM 1:45 PM B3 A58 M 4:00 PM 6:50 PM B1 A12 M 4:00 PM 6:50 PM B2 A20 MW 11:00 AM 12:15 PM B1 A24 M 12:30 PM 3:10 PM B1 A31 M 7:00 PM 9:50 PM B2 A40 M 6:00 PM 9:40 PM B1 A49 MW 2:00 PM 4:20 PM B3 A50 M 7:00 PM 9:50 PM B3 A9 M 5:00 PM 6:50 PM B3 A53 MW 3:00 PM 3:50 PM B2 A13 M 12:30 PM 3:10 PM B3 A17 M 6:00 PM 9:50 PM B2 A33 M 2:00 PM 3:50 PM B1 A35 MW 12:00 PM 2:20 PM B1 A17 M 2:00 PM 4:50 PM B1 A17 M 10:00 AM 12:50 PM B2 A19 MW 2:00 PM 3:15 PM B3 A44 MW 11:00 AM 12:15 PM B3 A44 MW 6:30 PM 8:50 PM B2 A53 MW 2:00 PM 2:50 PM

 

[SirJB7]

Hi, dondoggy!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


Despite of this, would you please consider pasting your sample data embedded into backticks (`) so as to be easier to read? Or better indeed, consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.


Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!

 

[NARAYANK991]

Hi All ,


The sample data is as follows :

[pre]

Blding Facil ID DysUsed St.time End time
B3	A3	M	17:00	22:45
B3	A8	MW	12:30	13:45
B1	A10	MW	8:00	9:15
B2	A36	MW	17:30	18:45
B1	A39	MTWR	8:30	10:30
B3	A42	MW	9:00	11:20
B2	A8	MW	9:30	10:45
B1	A30	MW	9:30	10:45
B2	A34	MW	11:00	12:15
B2	A41	M	11:00	12:15
B2	A47	MW	12:30	13:45
B2	A53	MW	9:00	9:50
B3	A8	M	19:00	21:50
B3	A31	M	9:30	10:45
B2	A44	MW	16:00	17:15
B1	A46	MW	11:00	12:15
B3	A54	MW	17:30	18:45
B3	A12	M	12:30	13:45
B3	A22	MW	12:30	13:45
B2	A38	MW	9:30	10:45
B1	A49	MW	17:30	18:45
B1	A49	M	9:00	10:40
B3	A53	M	19:00	20:40
B1	A9	MW	9:30	10:45
B2	A14	MW	12:30	14:50
B3	A22	MW	14:00	15:15
B3	A38	M	19:00	21:50
B1	A55	MW	11:00	12:15
B3	A58	MW	12:30	13:45
B3	A58	M	16:00	18:50
B1	A12	M	16:00	18:50
B2	A20	MW	11:00	12:15
B1	A24	M	12:30	15:10
B1	A31	M	19:00	21:50
B2	A40	M	18:00	21:40
B1	A49	MW	14:00	16:20
B3	A50	M	19:00	21:50
B3	A9	M	17:00	18:50
B3	A53	MW	15:00	15:50
B2	A13	M	12:30	15:10
B3	A17	M	18:00	21:50
B2	A33	M	14:00	15:50
B1	A35	MW	12:00	14:20
B1	A17	M	14:00	16:50
B1	A17	M	10:00	12:50
B2	A19	MW	14:00	15:15
B3	A44	MW	11:00	12:15
B3	A44	MW	18:30	20:50
B2	A53	MW	14:00	14:50

[/pre]
Narayan

 

[NARAYANK991]

Hi don ,


Can you post a sample result , considering the following input data ( which I have taken from your original post ) :

[pre]

B1	A49	M	9:00	10:40
B1	A49	MW	14:00	16:20
B1	A49	MW	17:30	18:45

[/pre]
Narayan

 

[SirJB7]

@NARAYANK991

Hi!

Thanks for the data arrangement.

Regards!

 

[SirJB7]

Hi, dondoggy!

I'll try to get back to you later, lots of columns and haven't find out a smart solution, hope that yet.

Regards!

 

[SirJB7]

Hi, dondoggy!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Scheduling%20Program%20-%20Finding%20Open%20Slots%20%28for%20dondoggy%20at%20chandoo.org%29.xlsx


The results are correct for the data you posted, but the only part pending is the generation of unique combinations of Building & Facility ID in worksheet Unique columns A:B, and update properly columns C and G:M of same worksheet.


Tomorrow I'd go on with this, I think; otherwise on Saturday if I'm available.


Regards!

 

[SirJB7]

Hi, dondoggy!

Done, modified and uploaded. Please download again the updated file from same previous link. Just advise if any issue.

I changed the data in one row of B3_A8 so as to test summary of days.

Regards!