Home > Templates > Holiday > Dinner Planner

Holiday Dinner Planner

Use this Microsoft Excel workbook to plan the timeline for holiday meal preparation. Enter the dinner time, then list the menu items and the time for each step. Print a timetable list, or a Gantt chart

Note: To organize your weekly meal planning, ingredients, and shopping list items, go to the Weekly Meal Planner page.

Enter the Dinner Time

Cooking a big dinner, with lots of dishes, is like any other project - you need to coordinate all the activities, and people, and resources, to make sure the project is a success!

In some projects, you decide when to start, calculate how long each step will take, then estimate when the project will be finished. Since the goal of this project is to serve dinner at a specific time, we’ll plan the schedule backwards, instead of forwards.

So, the first thing you’ll do in this meal scheduler is to enter the time that you want to serve dinner.

All the other times will be calculated, based on that dinner time.

Select Your Dinner Time

To select a dinner time, follow these steps:

• Go to the DinnerPlan sheet
• At the top, select a time from the drop down list in cell H2.

The drop down list was created with Data Validation, and the times are in a table on the Lists sheet.

You can edit that list of dinner times, on the Lists sheet, if needed.

Add List Items (Optional)

There are meal items, time, cooking locations, and process lists, on the Lists sheet. You can add more items, if you need to.

Note: The lists are in named Excel tables, so they will expand automatically.

• For example, in the Food Items list, I've listed main dish and side dishes for the main meal, but you could add pre-dinner snacks to this list too. Then you'll remember to put them out, before your guests arrive!
• Or perhaps you like different vegetables than the ones in the sample file. You can replace the items in the list with your own favourites.

Add Items to Lists

On the Lists sheet, add or remove items in the Location and Food_Items list, if needed.

For example, if you have an electric pressure cooker, and will use it for some of the meal items, add that to the location list.

To add an item to one of the lists on the spreadsheet, follow these steps:

1. Click in the blank cell below the last item in that list.
2. Type the new item, and press the Enter key.
3. Click on any item in the list, and click the arrow in the list heading
4. Click Sort A toZ.

The Dinner Plan

Whether your project is preparing a holiday dinner, or a software release, or anything else, you need to know what steps are involved.

In this Dinner Planner, the preparation steps are entered on the DinnerPlan sheet.

• To get started, go to the DinnerPlan sheet
• Take a look at the dinner plan steps that I've entered there, from one of our Thanksgiving dinners

This is where you will enter all the preparation steps for each food that you'll serve, with the time required for each step, and other details.

• You'll enter items in the green cells (instructions below).

• There are hidden columns (I:N) that contain formulas.
• You can leave those columns hidden, so you don't accidentally overwrite the formulas.

Enter Meal Item Details

For each food item that is on your menu, enter all the preparation activities on the DinnerPlan sheet.

• Important: Type in the green cells only-- the grey cells have formulas..

For example, there are 5 preparation steps for the Turkey, and those steps must be done in the following order:

1. Prepare stuffing
2. Stuff the turkey
3. Cook the turkey
4. Let the turkey stand for 15 minutes after cooking
5. And finally, carve the turkey.

NOTE: It doesn't matter what order you enter the food items in the list, because the list will be sorted later.

Enter First Activity

For each dinner preparation step, you'll enter 5 pieces of information:

• Item - what food you're preparing
• Activity # - what step number this is, for this food item
• Activity - short description of the preparation step
• Location - where this step takes place, such as the oven, counter, or stove.
• Minutes or Hours - estimated time this step will take to complete

To enter the first activity, follow these steps:

1. Click in cell C9, then click the drop down arrow, and select Turkey.
2. Press the Tab key, and in the Activity Number column, type 1
3. Press Tab, and in the Activity column, type Prepare Stuffing.
4. Press Tab, and in the Location column, click the drop down arrow, and select Stove
5. Press Tab, and in the Minutes column, type the estimated number of minutes that step will take -- 30 minutes in this example.
   • NOTE: For longer tasks, you can type the number of hours instead, such as 4.25 hours to cook the turkey.

Enter Remaining Activities

Move down to the next row, and repeat those steps, to enter the other 4 preparation steps for the turkey, and the other meal items.

Then, enter all the food preparation steps for all of the remaining meal items

Sort the List

After all the preparation activities have been entered, follow these steps to sort the timetable.

1. Select a cell in the Start Time column
2. On the Excel Ribbon, go to the Data tab
3. In the Sort & Filter group, click the A-Z button, to sort the list, from the earliest time, to the latest time.

Print the Schedule

After the meal items have been entered, and the list is sorted by Start Time, you can print the schedule. There are two options for printing:

• PrintedPlan -- a printed list of preparation steps
• TimeTable -- a Gantt chart with the schedule in visual form.

To go to either printout sheet, click the link in column O, at the top of the DinnerPlan sheet.

Print Plan

Here is the printed timeline, showing:

• When the food preparation has to get started
• All the steps for getting the meal ready, and when each step starts.

TimeLine

Here is the dinner preparation schedule in a Gantt chart timeline.

This visual plan gives you a good overview of:

• which steps take the longest times
• which activities depend on others steps being completed

How the Dinner Planner Works

If you'd like to see how the Dinner Planner formulas work, this section shows some of the details.

You don't need to read this section though -- you can just ignore the formulas! Go to the next section, and download one of the dinner planner templates, and get your dinner started!

Time Calculation Columns

On the DinnerPlan sheet, are hidden columns (I:N) that have the time calculation formulas.

To see the formulas, unhide columns I to N, and the formulas are explained below..

Time (Hrs)

The time required for each step is entered in either column G OR column H:

• G - minutes required to complete the task
• H - hours required to complete the task

To calculate the activity time in hours, the following formula is in cell I9:

• =IF(AND(G9="",H9=""),"",IF(G9<>"",G9/60,H9))

Here's what that formula returns:

• If both G9 and H9 are empty, the result is an empty string
• If there is an entry in the Minutes column (G9), that number is divided by 60, to get the time in hours
• If there is an entry in the Hours column (H9), that number is used

Item Time (Hrs)

The total time required for each food item is calculated in column J.

Here is the formula in cell J9:

• =IF(I9="","",SUMIF($C$9:$C$31,C9,$I$9:$I$31))

Here's what that formula returns:

• If cell I9 (total hours) is empty, the result is an empty string
• Otherwise, the SUMIF function returns the total hours for the food item in column C

Lead Time (Hrs)

The lead time required for each food item step is calculated in column K.

Here is the formula in cell K9:

• =IF(I9="","", SUMPRODUCT(--($C$9:$C$31=C9), --($D$9:$D$31>D9), ($I$9:$I$31)))

Here's what that formula returns:

• If cell I9 (total hours) is empty, the result is an empty string
• Otherwise, the SUMPRODUCT function returns
  • total hours (column I)
  • for the food item in column C
  • where the step number (column D) is greater than the step number in cell D9

Start

Next, the Start time for each food item is calculated in column B.

Here is the formula in cell B9:

• =IF(I9="","",$H$2-(K9+I9)/24)

Here's what that formula returns:

• If cell I9 (total hours) is empty, the result is an empty string
• Otherwise, the result is:
  • the Dinner Time (H2),
  • minus the (Lead Time + Total Time) divided by 24

End

Finally, the End time for each food item is calculated in column L.

Here is the formula in cell L9:

• =IF(B9="","",B9+I9/24)

Here's what that formula returns:

• If cell B9 (start) is empty, the result is an empty string
• Otherwise, the result is:
  • activity start time (B9),
  • plus Total Time divided by 24

Description and Start Hr

The final two formula columns create the labels and data for the Gantt chart.

This formula in cell M9 combines the food name, and activity description, to use for the Gantt chart label:

• =IF(I9="","",C9 & " - " & E9)

This formula in cell N9 converts the start time to a decimal number:

• =IF(I9="","",B9*24)

Video: How to Make a Gantt Chart

This video shows how a Gantt chart is built, and for written steps, you can go to the Gantt Charts page.

Get the Workbooks

1. Basic Planner: Download a copy of the free Excel Holiday Dinner Planner. The zipped file is in xlsx format, and does not contain any macros.

2. With Recipe Finder: Download a copy of the free Excel Holiday Dinner Planner and Recipe Finder. The zipped file is in xlsmx format, and contains macros and connections for the recipe finder.

3. Excel 2003: Download a copy of the free Excel Holiday Dinner Planner and Recipe Finder for Excel 2003. The zipped file is in xls format, and contains macros and connections for the recipe finder.

Note: To organize your weekly meal planning, ingredients, and grocery store shopping list items, go to the Weekly Meal Planner Template page.

Related Links

Data Validation

Sort a List

Chicken Dinner Planner

Christmas Planner

Weekly Meal Planner