Home and Learn: Microsoft Excel Course
An Excel Timetable Project
In this Excel timetable project, we'll set ourselves some chores to do around the house. We'll plan an exact time to start a task, and how long it will take to finish. We'll be adding one time to another.
So create the same spreadsheet as the one in the image below (you don't need to use the same colours):
What we're going to do is enter a Start Time for our chores. This will be 9 in the morning. Then we'll estimate how long it takes to wash the pots, which we'll place in the Time Job Takes column. We'll add the "Time Job Takes" to the "Start Time" to get a new start time for the Hoover chore. But you'll see how it works as we go along.
The first thing to do is to format the Start Time column:
- Highlight the Start Time column, from cell B3 to cell B8
- From the Excel menu bar, click on Home
- Locate the Number panel:
Click on the arrow (circled above) to bring up the Format Cells dialogue box, and then click the Time category:
- Under Category on the left, click on Time. Under the time Types on the right, select the first one.
- Don't click OK yet, but have a look at the time format that Excel is going to enter:
Excel will enter the hours, then minutes and the seconds. We don't need the seconds. Unfortunately, this version of Excel doesn't give you a time format without seconds. To remedy this, click on Custom under the Category list on the left. Then, under Type, select "h:mm AM/PM", as in the image below:
Click OK when you're done. We'll now enter our first time.
- Click on cell B3, then click inside the formula bar
- Type in 09:00 (the colon in between the numbers is important)
- Press the enter key on your keyboard
- Excel will now see cell B3 as a time - 9.00 AM
There is a simpler way to format a cell as Time, though. Try this:
- Click inside cell C3
- Click inside the formula bar
- Type in 0:15
- Press the Enter key on your keyboard
Because you included the colon (:), Excel knows that you want to format the cell as a time. The 0:15 then means 15 minutes (We'll assume that we're very fast at washing pots - it's all that practice!).
But your spreadsheet will now look like this:
If we started at 9.00, and the job took 15 minutes, the next start time will be 9.15. We can enter a formula for this:
- Click into cell B4 to highlight it
- Then click inside the formula bar
- Enter the following formula:
= B3 + C3
- Press the enter key
- Excel will place a time of 09:15 AM in cell B4
The start time for our next chore, then is 9.15 AM. We can use AutoFill for the rest of the B column:
- Click in to cell B4 on your spreadsheet
- Move your mouse the bottom right of cell B4, and the pointer will change shape. When you see the black cross, the AutoFill cursor, hold down your left mouse button and drag down to cell B9
- Let go of the left mouse button and Excel will AutoFill the other formulas
Because we haven't yet entered any other figures for the "Time job Takes" column, a time of 9.15 will appear in all the cells.
- Click onto cell C4 on your spreadsheet
- Now click in to the formula bar at the top, and type in 01:00 (meaning one hour)
- Hit the Enter key on your keyboard and Excel will change all the cells from B5 to B8 to a time of 10:15 AM
- Your spreadsheet should look like ours:
Complete the rest of the spreadsheet for yourself. Enter these times in the C column:
Rest: 30 minutes
Dust: 30 minutes
Windows: One hour
Rest 30 minutes
If you complete it all correctly, you should have a spreadsheet like ours in the image below:<
In the image above, you'll notice that there is a time in cell B9 of 12:45 PM. You should easily be able to get the same figure in your spreadsheet!
Working with date and times can be quite tricky. But it's well worth getting the hang of. We'll move on, though, and have a go at financial functions in Excel.
Financial Functions in Excel -->
<--Back to the Excel Contents Page