Excel Student Time Tracker: Spreadsheet Day 2011

SpreadsheetDay82 Happy Spreadsheet Day! I hope you’re making time to cell-ebrate this special day. October 17th was selected as Spreadsheet Day, because that is the date that VisiCalc was first shipped.

Student Spreadsheets

The theme for this year’s Spreadsheet Day is Student Spreadsheets. If you have uploaded a free, useful template or add-in for students, or posted a spreadsheet tip, please send me the link so that I can share it.
Or, post your links/tips on Twitter, using the hashtag — #spreadsheetday – so we can find them.

Student Time Tracker

My contribution for Spreadsheet Day 2011 is a Student Time Tracker. You can keep track of your lecture hours, and course work hours, to see what the weekly totals are.
To start, you’ll enter the Semester start and end dates, in the blue cells.
Next, enter your courses, and the scheduled lecture and lab hours per week.

Add Your Assignments

As the semester progresses, enter any assignments that you get, and other tasks, like preparing for tests and exams. As you finish your assignments, enter the completed date and actual task time. This will help you improve your time estimating skills.
For large assignments, you can use the Course Work Time Estimator sheet, to enter all the steps, and the time each step should take. Then, add a buffer percentage, to include extra time in the estimate. This will cover all those little things that can go wrong along the way.

Check the Weekly Hours

On the Weekly Hours Time Estimator sheet, you can see the total hours for each week in the semester. The Work Hours are calculated by using the SUMIF function to get the hours for each week.
At the top of the sheet, enter your target hours for each week – the maximum number of hours that you want to spend on classes and assignments.
In the screen shot below, the target is 25 hours (that’s pretty low!), and there is conditional formatting to highlight weeks that exceed that target.
If you see a heavy week coming up, you might be able to complete some assignments early, to ease the workload.

Download the Student Time Tracker

To see how the time tracker works, you can download the Student Time Tracker template. Go to my Sample Files page, and in the Functions section, look for FN0037 Student Time Tracker
The file is in Excel 2007/2010 format, and zipped. There are no macros in the file.

Plan Your Holiday Dinner in Excel

Mock me if you will, but I use Excel to plan the timing for our holiday dinners. Monday was our Canadian Thanksgiving, so it was the perfect occasion to dig the planner out again.
You know that it’s a delicate juggling act, trying to get everything cooked and on the table at the same time. Then, halfway through dinner you realize that the dinner rolls are still in the oven. Oops!
To prevent the senseless loss of dinner rolls, and help things go smoothly, I use my Excel Holiday Dinner Planner.
Yes, it takes a few minutes to set up, by entering all the dinner items, and the preparation steps, but it’s time well invested! If you’re like me, and don’t vary the holiday menu too much, you can reuse the worksheet, for every holiday.

Calculate the Start Time

Once the sheet is set up, you simply select the time that you want to serve dinner, and the Excel dinner planner calculates the preparation start time.

Follow the List

With the planner, you’ll have a complete list of dinner items, with preparation start and end times. Follow the list, and you won’t be likely to forget those dinner rolls in the oven.
You can find more instructions, and download links, on the Excel Holiday Dinner Planner page on the Contextures website.

Delete Orders with Excel Data Entry Form

Long ago, Dave Peterson created an Excel worksheet data entry form, so you could enter records on one sheet, and store the data on another sheet. Then, you can hide the data entry sheet, so users don’t accidentally change any of the old records.
There have been a few version of the data entry form file, including the the previous version, in which you could also update the selected record. This way, the data sheet can still be hidden, but users can make changes to the existing records.

Delete the Current Record

In a comment, Bryan asked for a Delete button too. In this new version, that feature is added. (Thanks, Bryan, for the suggestion!) Use this version if you really trust your workbook users – and keep good backup files!
When you click the Delete button, a message appears, asking you to confirm that you want to delete the record.
If you click No, the deletion is cancelled.
If you click Yes, the record is deleted from the database worksheet, and the data entry cells are cleared.

Download the Worksheet Data Entry Form

To see how the data entry form works, you can go to my Cotextures website, and download the Worksheet Data Entry Form sample file. The file is in Excel 2003 format, and is zipped. After you unzip the file and open it, enable macros, so you can use the worksheet buttons.

Excel Loan Payment Calculator

image Can you afford that new car? Or maybe you loaned money to one of your kids, and you want to calculate a repayment schedule. (Oh yes, they will stick to the plan, without fail.)
To help you figure out the payment amounts, here is a nifty Excel loan payment calculator. (The kids will think you’re cool when you say “nifty”.)
It uses the PMT function to calculate the payment amount, and you can enter the variables:

  • Loan Amount
  • Payment Frequency
  • Term (Years)
  • Annual Interest Rate

How the Loan Payment Calculator Works

Of course, if you are the Bank of Dad, you might offer a lower interest rate, so you can adjust any, or all, or the green cells.
Type in any cell, except Payment Frequency, where you can select from a drop down list of options.
The Lists sheet has a lookup table of frequencies and number of payments.
Based on the frequency that you select, a number of payments per year is calculated in cell E5, using a VLOOKUP formula.
The payment amount is calculated with the PMT function:
I added a minus sign before the present value variable, so the monthly payment is shown as a positive number. You can omit the minus sign, to show the payment as a negative number.

Download the Excel Loan Payment Calculator

To see the formulas, and experiment with the calculator, you can download the Excel Loan Payment Calculator sample workbook. The file is in Excel 2007 format, and zipped. The calculator uses the IFERROR function, so you’ll need to change that if using an earlier version of Excel.
If you have any comments, or suggestions for enhancing the calculator, please let me know in the comments. Thanks!

Excel Weight Tracker Metric

Last year, you might have seen the Excel Weight Loss Tracker workbook that I posted, which used inches and pounds as its measurements. Even though we use the metric system in Canada, we still track our weight in the Imperial system. (I’m not sure why.)
In May 2010, I posted the weight loss tracker in a Stone/Pound version, for the people who use that system.
Note: Please consult your doctor for recommended target weight and weekly weight loss goals. This workbook is designed as a recording tool only, based on the goals that you set with your doctor’s advice.
[Update] The latest version of this workbook lets you select pounds or kilograms as the measurement. To download, see the link at the end of this article.

Metric Weight Tracker

If you’d prefer to track your weight using the metric system, you can download the latest version, which uses kilos. In this version, you can enter your height in centimetres, and your weight in kilograms. The BMI and ideal weight range are calculated based on those measurements.
The good news is that your weight is a lower number in kilograms, compared to pounds. The bad news is that your pants don’t fit any better!

Download the Excel Weight Tracker Metric

You can download a zipped copy of the Excel weight loss tracker, and try it for yourself. The sheets are protected, with the green cells unlocked, and there’s no password on the worksheets.
There are versions for Excel 2007/Excel 2010, and Excel 2003 on the Contextures website: Excel Weight Loss Tracker
And remember, I’m not a trained medical professional, so check with your doctor for personalized weight management advice.

Too Few Rows in New Excel Workbook

In Excel 2007 and Excel 2010, when you create a new workbook, there should be 1,048,576 rows on the worksheet.
However, one of my clients was creating new files in Excel 2007, and the sheets only had 65,536 rows, just as they did in older versions.
Perhaps you don’t need more rows than that, but if you’ve paid for a shiny new version, you’d like access to all of its features!

Solve the Too Few Rows Problem

At first, we thought the problem might be an old Excel 2003 template, that was starting automatically, and being used for the new workbooks. A search of all the Templates folders didn’t turn up any suspects, so that theory was wrong.
Finally, we discovered that the default format for saving files was set to Excel 97-2003 Workbook (*.xls).
To get the full-sized Excel 2007 worksheets, go into the Excel Options, and in the Save category, select one of the newer formats as the default for saving files.
Click OK, to close the Options window, and when you create a new workbook, its sheets will have 1,048,576 rows.

Set Up Golf Tee Times in Excel

golficon There’s a Golf Tee Time Excel workbook on the Contextures site, that I’ve updated, to add a few new features. You’ll list all the players, then assign groups of players to a selected tee time.
The new version has data validation in column C, which limits the selection to 4 names, and you can’t mark a name that is already booked. The time selector only shows tee times that haven’t been assigned.

Assign Golf Tee Times

On the MemberList sheet, you can enter a list of names in column B. Mark up to 4 names, by typing an X in column C.
Then, select a start time for the marked names. The drop down list only shows the available times. In the example below, the 8:20 AM time has been filled, so it’s not in the drop down list.
Finally, click the Book Times button, to add the marked names to the selected time slot.

How the Tee Time Selector Works

When you click the Book Times button, a macro filters the marked names into a hidden column (H) on the MemberList sheet. The criteria range for the advanced filter is range F3:F4.
Then, those names are copied, and transposed onto the TeeOffTimes sheet, in the row that matches the selected time.
After pasting the names, the Mark column is cleared, so you can select the next group of names.
To start over, clear all the names from the TeeOffTimes sheet (green cells in the screen shot above).

Download the Sample File

To see the golf tee time workbook, and the time booking macro, you can download the Golf Tee Time Excel workbook. The file is in Excel 2003 format, and is zipped. It contains macros, so you’ll need to enable macros to test the time booking feature in the sample file.

Excel Weekly Meal Planner Update

image There is an Excel weekly meal planner on the Contextures website, in which you can select meal items, and create a weekly shopping list.

Shopping List Problems

In December, I added an online recipe selector, created by Jimmy Peña, and described the new feature in a blog post.
This weekend, Alyssa pointed out a problem — if you select a meal item twice, it’s only added to the shopping list once. That could cause problems, if you run out of food on Friday, and have hungry and cranky children waiting for their dinner. Thanks Alyssa!

Fix the List

To show the correct quantities in the shopping list, I changed the heading in the original quantity column, from Qty to Meal Qty.
Then, I added a new column, with the heading Qty, and a formula to multiply the Meal Qty by the List qty. The formula in cell H2 is:

Download the Updated Excel Meal Planner

You can see the full details for the Excel Weekly Meal Planner on the Contextures website, and download an updated copy to help plan your meals.

Use Excel Scroll Bar to Trim Christmas Tree

An Excel scroll bar can be used for practical (and sometimes boring) things, like testing the effect of price changes, or adjusting a chart’s date range. But this is the festive season, so let’s use a scroll bar for something more, well, festive!

Trim the Tree

In this example, instead of accounting and finance, you’ll see how to use an Excel scroll bar to decorate a Christmas tree, without macros. Unfortunately, it can’t make hot chocolate or eggnog, so you’ll need to provide your own.
It’s not just for the holiday season though — the sample file has useful features that you can adapt to other workbooks too:

  • Scroll bar lets users change a number quickly and easily
  • A text box that displays a changing message based on VLOOKUP formula
  • conditional formatting shows hidden cells when target number is reached
  • named ranges make it easy to work with specific cells


Watch the Video

To see how the tree trimming scroll bar works, you can watch this short Excel video.
Video Link on YouTube:  http://www.youtube.com/watch?v=6UCJCB2vtHA

Excel Scroll Bar Sample File and Instructions

For instructions on creating the Excel scroll bar file, and to download the sample file, please visit the Contextures website: Excel Scroll Bar Christmas Tree Example.

Excel Weekly Meal Planner With Recipe Selector

imageThe problem with putting a lovely red geranium on your table is that you can end up remodelling the kitchen! (Do you remember that magic geranium fable?)
Anyway, a while ago, I posted my Excel Weekly Meal Planner, which let you select meals for each weeknight, and print a grocery shopping list. All was well in my kitchen, as I happily planned my meals with that useful workbook.
Then, JP from Code for Excel and Outlook, sent me a “geranium” — a copy of my Excel Christmas Planner, with a fancy new Excel Recipe Selector worksheet. JP’s workbook has code that looks up recipes via web API, and returns the result to Excel.
For example, if you’re tired of steamed carrots, you can enter “Carrots” in the Recipes worksheet, and find more exotic recipes online.
Thanks JP! Your enhancement works great, and now it will be easy to spice up those holiday meals.

Remodelling the Excel Weekly Meal Planner

Yes, JP’s Recipe Selector makes the Holiday Dinner Planner better, but it made my Excel Weekly Meal Planner look shabby! It needed a recipe selector too. In his blog post, JP showed the code for using the API, and had a link to another function that’s required.
So, I copied the Recipes sheet from JP’s workbook, and inserted it into my weekly meal planner file. It worked great in its new location, and I just had to change a couple of references in the code.
But…now the rest of the weekly meal planner looked a bit run down, so I decide to remodel it. After a couple of coats of paint, a new backsplash and a shiny new pivot table, it’s ready for the open house.
You can tour the remodelled version on the Excel Weekly Meal Planner page on the Contextures website, and download a copy to help plan your meals.

Watch the Excel Weekly Meal Planner Video

To see a quick overview of how the Excel Weekly Meal Planner works, you can watch this short video.