Do you want to learn how to make schedules? (A guide)
 

Schedule creation is broken down into a few steps:
- Planning
- Set up workpaper
- Schedule the games
- Verify the accuracy of the schedule
- Create the open office file
- Save with the correct file names
- Test in game

Some schedules can be created in a few hours; some take a long time over several days. The key is to figure out what type of schedule you want and plan out as much as you can before you start scheduling.

I will do a step by step process of how I create a schedule from a real schedule request. I hope that by doing this more people will be able to make schedules.


First, you find a schedule that needs to be created. This looks like the oldest schedule request I haven't attempted.


I will post replies to this thread to show how I go about setting this up:




=============================================


Schedule Request for Victorious Baseball Association

Hi! I'm the commissioner of the VBA and was wondering if someone could make the following schedule for me:


A 10 team 162 game schedule
6 games every week
Everyone plays in 1 day or no one plays in 1 day
Everyone plays at the same time each day

Thanks!

Ian

Step 1 - Planning
 

The first thing you need to do is determine how your league will be set up and how many games each team will play.

In our example, we have 10 teams playing 162 games. This set up is pretty straight forward since each team plays everyone 18 times.

9*18 = 162

We have one SL, one division, 10 teams.

There are a few other stipulations for this league:
- 6 games per week, 1 off day
- everyone plays the same day, everyone has the same off days.

This makes the schedule really easy to create. I would choose Monday as the day off, so the schedule would start on a Tuesday and have TWTh and FSaSu series.

54 series per team 2 per week, 27 weeks.

Once we know we need 18 games against each team, 9H/9A, we need to set up the match ups for each team.

Part 2 - Setting the series match ups
 

Now that we know we need 18 games vs. each of the 9 teams in the league, we need to list out all the games that can be scheduled; for all the teams.

I group them by series.

On the Matchup tab, you can see all the series that will be scheduled on this schedule.

We start with team 1:

Team 1 plays 3 home series and 3 road series vs. all the teams.

In the first two cells of row 1; you can see 1 2 This translates to 1 @2

That's a 3 game series.

For each team, team 1 plays 3h/3a series. That looks like this

1 2 2 1
1 2 2 1
1 2 2 1


Repeat for all 9 opponents.

For team 2, all 18 games vs. team 1 are already scheduled. So we start with 23 32.

For team 3, all games vs. team 1 and 2 are accounted for, so we start with 34 43.

Once you get to team 9, we have

9 10 10 9 three times.

Team 10 has nothing listed since all of their games are accounted for.

I use this sheet to keep track of the series I've already scheduled.



The next step is determining who plays who and when.

There is one more step before moving on. I try to verify I have the correct number of series on my "matchup" tab. I use the COUNTA function in excel.

A few rows below the matchups, in A30, I use this formula:


=counta(a1:aaa29)

This gives a result of 540

This is a count of all the numbers on the screen. We need to translate to total games.

Divide this 540 by 2 = 270 (This is because there are two numbers on the screen for each series (1 2 2 1) etc)

270 is the number of total series in the league schedule. Each series has 3 games.

270 * 3 = 810

If you look on the info page, we already proved out that there are 810 total games.

10 teams times 162 games each = 1620. Divide that by 2 = 810.



In the next installment, I will go over the workpapers for the Daily Schedule, the Team Rotations, and the Home/Away splits.

Part 3 - Wrap up the Planning and set up
 

After you have all the series mapped out and you verify that you have all the games on the matchup tab, it's time to move onto setting up the rest of the work papers.

I must stress its imperative that you have the correct amount of series/games on the matchup tab. If you have too few or too many games there, this isn't going to work.


There are three more tabs added:
- Schedule
- HA
- Ten team rotation


The schedule is where we will paste our games. Since we have 5 games a day, I just put a new week every 10 lines. Just have to make sure to leave enough lines for each day's games.


The HA is where I track the teams home and away series. For 3 games series like this schedule, I try to keep it at a maximum of 4 series in a row at home or on the road. With 10 teams this is pretty straight forward.


The Rotation tab is the key to the schedule. On this tab, we have a full rotation of each team playing every other team. For 10 teams, you will have a block of 9 series. For 16 teams, it will be a bock of 15 series. This might be the constraint when creating your own schedule. If you get stuck trying to create this, there are websites that will create schedules for you . I just use them to create a schedule with 1 game vs. each team. So for 10 teams, it's a 9 game schedule. I just take that schedule and make it into my rotation template.

On the Rotation tab, we have 9 blocks. Each team plays every other team 6 times. On the right hand side, I created a list of series. This is how I will schedule my games. I use a random number generator and plot out 1-9 in row AB. I do the same thing for the row AC.

I try to keep the end of row one and the beginning of row two so that the teams don't play within 2 series of each other. So the end of row 1 is 5 3. I don't want the beginning of row 2 to be a 5 or a 3.

Our schedule will be created using the rows on the right and the blocks of games that is corresponds with on the left.

We will touch on the HA tab once we start scheduling games.

I'm new to OOTP .. wondering if a home-brew sked can be created to allow for doubleheaders and fewer weeks. Ultimately I want to create a 154-game slate that stretches 24 weeks, so that I can play with the old-school mid-April opening day.

Part 4 - Scheduling Games/Series
 

As I talked about in part 1, I set up the schedule using series as the building blocks. In this part, we are going to take the series blocks we created in the 10 team rotation and use them to start scheduling games.

The first thing you do is go to the 10 T Rotation tab. You can see on the right the first number in the first row is 9. That means on the left, we are using block 9 as the first set of games.

Copy those 10 cells, go to the Schedule tab, and paste them into cell E2. This is the first Tuesday of the first week. (remember we are playing 6 games a week, T W Th, Fr Sa Sun.

Right now we have 5 games scheduled on the first Tuesday. I usually flip a few numbers so it's not all the low numbered teams on the road.

You can either just manual pick them or use a RNG to decide which ones to use. For this, I will switch teams 1, 2 and 4 at home.

This leaves us with these 5 games on Tuesday:
9 10 8 1 7 2 3 6 5 4

I highlight all 10 numbers to make sure I am using each team once. For this league, it's 10 numbers, sum is 55. If you had team 4 in there a twice, the sum wouldn't be 55.

After you are satisfied with these five games, we need to mark them off on the "matchup" tab.

On the matchup tab, highlight the series 9 10. 8 1. Repeat for all 5.

Since this is the first series, all 5 of those will be available. Later in the schedule, maybe all 3 series of 8@1 are highlighted and you have to change it to 1@8.

Back on the schedule page, copy/paste the games to Wednesday and Thursday.

Now we are going to use the HA tab. We want to keep track of what teams are home and away.

For the first series, these are the home teams. 1 2 4 6 10.

On the HA tab, in the first row, put an H in the columns for 1 2 4 6 10.

The HA tab has several formulas in it.
- In row 2, I have a Counta formula to count all the "h" in that column for each team. When you put "h" in c3, it will count that as a home series for team 1.
- In column M, I have another counta. It will count everything in that row.
-There are two conditional formatting sets. In Column M, once it hits 5, it will trigger the CF. I do this so I can make sure that I have exactly 5 home teams that bar.
-The second CF is for every bar inside the table. If you type h, it will turn blue.


So when you type in H for the 5 home teams, it will add 1 to row 2, it will color it blue (so you can easily see the Homestands and Roadtrips), and it will color column M orange once it hits 5.

These are just checks for me to make sure everything is right. For a 10 team league its not that pressing, but if you have 40 teams, its more important.

Lastly, we go to the 10 T Rotation tab. Highlight Group 9. Highlight the number 9 at the top of the row.


We repeat the process for the Fr, Sa, Sun series. For the second series, we will use the second number in row AB (1)

The following blog by former forum member gmo—who was a wizard at creating custom schedules using what might be termed a 'mathematical' approach—may be of interest in getting a feel for the process involved in creating a schedule.

Making Baseball Schedules
.
.

Part 5 - Finishing the scheduling of games
 

Where we left off in part 4, we had schedule 1 series for each team. We have 53 more series to schedule.

What we do next is look on our 10 T Rotation. The second number in the first row is a 1 (9 was first series). We copy the 5 series from block one into the first Friday in the schedule:

1 10 2 9 3 8 4 7 5 6

These are the match ups for the next series. This is the first real decision you have to make. Do you want teams to go back and forth for H/A. Do you want to try to build out some 3-4 series home stands?

For the most part, I try to do two things when scheduling after the first series:
1. Keep the teams with a similar amount of home series as I go (row 2 on the HA tab will tell you how many series they have at home)

2. Keep teams at home or on the road for 2 to 4 series.

Again, it's up to you. I try to balance it out earlier in the season because you have less options later in the schedule since team 2 has to play at team 4 when there is only one series left (or any other team as an example)

So the matchup 1 - 10 Both were at home in series 1. I just randomly choose who's at home this series.

Same with 3-8.

After you set who is at home on the schedule tab, go to the matchup tab and highlight the 5 series you selected.

Then go to the HA tab and put an H in the 5 teams that are home in series 2.

Then go to the 10 T Rotation tab, highlight block 1 and the number 1 in the first row.

Now you have two series scheduled.

Repeat this 52 more times :)

When you are the end, you should have all the match up highlighted, row two of the HA tab should say 27 for each team. Column M should be orange for each row.

On the 10 T Rotation page, you should have all six columns highlighted.


After you have everything set up and you feel you have the right amount of games for everyone, it's time to test the schedule.

Next time.

So now that you have all 54 series scheduled for each team, we need to make sure that every team plays exactly 162 games overall; and exactly 81 at home.

I do this by using the Cont+F in excel. On the schedule tab, make sure all of your cells are numbers and not formulas. Do this buy copying the whole page, and pasting using the "123" function. For this page, we usually hard type everything so it's not a huge issue.

After you do this, hit Cont F to bring up the search box.
Hit options
Check the "match entire cell contents" box

type: 1 in the find what box. Hit find all.

With hope, it will say 162 cell(s) found.

Erase 1, hit 2. Find all.

Repeat for the amount of teams you have. Make sure they are all 162.

That will test to make sure we all have 162 games. For the second test, make a copy of the Schedules tab.

Right click the schedules tab
move or copy
click the create a copy box
highlight (move to End)

Hit ok

It will created schedule(2) at the end. On this tab, delete columns
A
C
E
G
I
K
M

We will be left with only the HOME games of every series.

Do the control + F again. same process.

We should have 81 games for each team.


Sometimes you will have a few teams with 84 and a few with 78. Or more or less.

When this happens, you have to trouble shoot and figure out what happened. Most of the time, you will have one team with 84 home games, and one team with 78 home games.

So instead of 9H/9A against say, team 3, team 1 has 12H/6A

This happens occasionally. What I do is do a replace on the back up schedule.

So lets create a new copy of the schedule. So we have Schedule(3) tab.

Hit control F

Hit the Replace tab

Make sure "match entire cell contents" is checked.

Find what: 1
Replace with: 1 (but format the cell color to some warm color, like light orange

Find what: 3
Replace with: 3 (format the cell color to some cool color, like light blue.

You will replace all 162 games for each of these teams. The 18 games they play against each other will be easy to see, you will have an orange cell next to a blue cell 3 games in a row.

Look at see, we will probably see four series of 3-1 and two series of 1-3.

Change one of the 3-1's to 1-3 (the whole series).

Save, create a new copy, and then test to see if they have the same amount of home games.


Once all the teams have the same amount of games (162) and home games (81), we are ready to move on.

On thing, this won't test that we play the correct amount of games vs. each team. But using the matchup tab, it's hard to schedule the wrong amount of games vs. several teams and have everyone have the 162 - 81 answers when we roll the test.


From here, we take our schedule and make 4 columns in an excel file that we will copy and paste into the Open Office file.

From the open office file, we will save it as the .lsdl file

Thank you very much for this guide thehip41!!! Now, can you do an other example with a MLB schedule style (with 30 teams) please???

The concept for all the schedules is the same. Once you get into 30 teams, 15 in each division, thats where you have to get creative.


Since there are 15 teams in each league, there needs to be an interleague series every time.

If every team in the NL played ever team in the AL, wouldn't be that big of a deal. Since only AL East plays NL East, you only have these matchups

1-5 play 16-20

etc.

Making each league 15 makes creating the schedule really difficult. You can just simply take a rotation like we have in the example and roll through it 6 times. In the current MLB set up, you are going to have to manually schedule out the interleague games first and then add the rest of the games to the schedule around it.

That means hand scheduling each series game by game.

If there is an easier way to do it I am unaware of it.

At this point we have verified our schedule is correct. All teams have the correct amount of home games and total games. We assume the games vs. opponents is correct because we used the matchup tab. We can test that after we import into the game as well.


Now what we will do is create a new tab, I usuallu call it Export. We need 4 columns.

Column A will be the Day of the schedule. I always start at 1.
Column B will be the time of the game
Column C is the Road team
Column D is the home team.

Make sure there are no equations in the export tab, just numbers.

For Column A, we need 5 games per day. Since our schedule is very balances, we play 6 days in a row, and then one off day. For this I do the following.

I type in

1
1
1
1
1
2
2
2
2
2
all the way through 6.

Then in the Row 31, just use this formula =7+A1

Copy that all the way down. It will give us 5 games a day for six days in a row and the 7th day off.

Each schedule is different.

When I have schedules that have varying days off, or 2 games one day and 5 the next, I simply take the amount of teams and divide by two. That is how many games you can have (the max) on a certain day. Then as you start copying, you can delete the extra games for certain days.

Now we have the days correctly listed in Column A. We can add times a few ways. You can make all the games at 7pm. so 1900.

Just type 1900 in the first column and copy it all the way down.

Or you can use the RNG to assign some times as 1900 or 1700 or whatever you want.

Regardless of how you decide, they go in the second column.

Games, we are going to copy from the schedule tab to the export tab.

On the schedule tab, erase the background fills for all the numbers. Then highlight the 5 games being played on day 1. Copy

Go to C1 on the export tab. Paste them there.

Then paste two more times on day 2 and day 3.

Once you have that done, I mark off those games on the schedule tab so I know I already copied them.

Do this for the rest of the games. Since we have no all star game, it's a simple matter of copying and pasting until we are done.

Once we have all the games in the export tab, I do a sum all equation on the bottom of each row. If they don't equal, something is wrong.

Another good check figure. We had 810 games we need to schedule (based on the calculations from the Info tab)
You can see the export file, it has 810 lines. We have all the games we are supposed to have.


Assuming they are equal, we are ready to copy this to the Open Office file template I created.

Team Rotations / Schedule Template
 

I will attach a file that has all the Team Rotations I have created

From a 4 team league to a 20 team league.


I have also attached the schedule template. I use Open Office for that file, so when you save this file, open the OO and copy that into there. I can't upload my OO file.


Create a copy of the template. From the export tab on the guide, copy and paste the first column into the space for the days. Repeat for the time, away team, and home team.


Once you have that set up, you need to change the parameters in the top section. The SL_D1, etc. Just change that line to match what your league has.

Once that is done, you (in open office),
1. hit save as
2. This is key here, change teh save as type to : text csv (.csv)
3. Name the file exactly what it says in the row near the top, SL1_D1, etc
4. add .lsdl to the end of your file name
5. uncheck "automatic file name extension"
6. Save
7. Click keep current format
8. character set (Western Europe (ISO-8859-1)
9. Field delimiter space
10 Text delimiter (nothing)
11. ok
12. ok


You now have an .lsdl file

PLace that in the schedules folder in your ootp 16 area. Now you can search for this when you are modifying a schedule.

This is great, I had no idea that .lsdl files were just .xls. Makes sense, but I just didn't realize how "easy" it was for us to make schedules ourselves. Going to bookmark and come back to this, as I'd love to do this.

This is amazing! Thanks! I've done a few schedules a long while back and gave up because the in-game editor is so tedious. This tutorial makes it a breeze. I've done three since reading this and none of them took more than an hour.

If you make new schedules, you should post them here. Joe will put them in the schedule master list.


Just create a new thread for each schedule and put what it is in the title. The more schedules we have available the better.

I will. I was going to post them when I got home last night but didn't have time. One of them is already on the list, but the other two aren't.

This thread has been really helpful in getting me started on schedule generation...many thanks to thehip41. I'm still fussing with trying to rearrange home vs. away so teams have as sensible a homestand/roadtrip pattern as possible; that seems to be the hardest part of the whole thing.

I've been trying to automate a lot of the tracking and double-checking, and after playing around with formulas--and relearning about array formulas in particular--I've come up with some features that might be helpful to others, so I'll share them here.

The attached file is a single sheet, with my in-progress schedule project on the left, laid out like the Schedule tab in thehip41's example. Next to it on the right are two trackers. On top is the same Home/Away view as in the example's HA tab, except that I have it automatically pulling home-team information, so I don't have to enter it manually (and keep changing it manually as I tinker with the schedule, which was one part that was proving a real hassle).

Below that is an automatic tally of how many series each team has scheduled against each other team, both home and away (the x-axis represents the home team). This should eliminate the need for manually crossing matchups off the matchup sheet from Part 2 above; I think it makes it possible to skip making that sheet at all.

The trackers are set up to show correct totals as green numbers and incorrect/incomplete ones as red ones. (The correct numbers are set in the conditional formatting; adjustments can be made there to the target numbers to accommodate different numbers of teams or schedule lengths or formats.) Also, boxes in the HA tracker will change color if a team is trying to host more than one series at the same time. I suppose an additional check could be added to look for cases where a team was scheduled for simultaneous home and away series; I would think that most of those would be caught by the series tracker below though.

Note that the trackers here are keyed to the first game of each three-game set only. That's working out fine for me in this example, because the schedule is being set up for three-game series only, all starting on either Monday or Friday. There is even simple interleague play included in this example, but no divisional play (the league is just 12 teams, split between two single-division subleagues). If you've got a more complicated setup, this may or may not be easily adaptable for you; but the formulas may at least help give you some ideas.

Oh, and if you've not worked with array formulas before...they're the ones in the curly brackets: { }. You don't type those characters when entering the formula though; what you do is type CTRL + SHIFT + ENTER instead of just ENTER and it will make it an array.

I'll be happy to answer any questions about what I've tried to do here.

P.S. I had to change the file extension to .xls to get it to upload, but you'll need Excel 2007 or later (or something else that's able to handle the .xlsx feature set) for it to work properly.

This spreadsheet looks very helpful, even for someone like me who has a lot of experience creating schedules and has a certain method I follow. I think this can enhance my process in the future. Thanks.