Coffee Break Computer

Excel 2007 Practice: Create a Worksheet with Dates & Days

Here’s the premise: you want to create a class attendance worksheet in Excel. The class meets Tuesdays and Thursdays from August 26 to October 14, 2008. The worksheet should have vertical headings with a Tuesday / Thursday date series, where the month and day displays (like 26-Aug, 28-Aug, or 8/26, 8/28). In the cells directly below those, just the day of the week for each date will display (like Tue, Thu). The attendees names would be in the A column, and the spaces to mark attendance would be left blank.

Create a Date Series

1. In cells B2 type 8/26 and in C2 type 8/28. They will probably automatically change to the default date format, but let them be, as you’ll be changing the format as soon as you fill in the series.
2. Select B2 and C2 by dragging the wide white plus over them.



3. Move your mouse over the fill handle, right-click and drag to the right 13 columns (to P). When you release the mouse, the context menu should appear. Choose Fill Weekdays.
4. Try not to click anywhere on the sheet – the date series you just created will stay selected, so you can immediately format the dates to display without the year.

Format the Dates

1. On the Home tab, Cells group, click Format, then Format Cells (it’s the last item in the Format menu).
2. In the Format Cells box, you should be in the Number tab. In the Category: section, make sure Date is selected.
3. In the Type: section, choose the format shown with 14-Mar (day/month). The Sample area shows a preview of how the selected cell will look with the format you choose.
4. Click OK to apply. All the dates should be formatted to 26-Aug, 28-Aug, 2-Sep, etc.



5. With the dates still selected, this is a good time to center them; in the Home tab, Alignment group, click the Center button.

Add the Day of the Week and Create a Custom Date Format

1. Move to or click on cell B3 (directly under the first date, 8/26).
   Type: =B2 then push Enter. This will display whatever is in B2 in B3. Now you’ll format B3 to display only the day.
2. On the Home tab, Cells group, click Format, then Format Cells.



3. In the Format Cells box, you should be in the Number tab. In the Category: section, select Custom.
4. In the Type: section, type in ddd. The Sample area should display Tue. (If you wanted the day spelled out, use dddd.)
5. Click OK to finish.
6. Center B3.
7. Now you’ll fill in the rest of the days from B3. With the cell pointer on B3, click the fill handle, and drag down to under the last date. All the days should fill in, with your custom date format and centering applied.

The last steps would be to add borders to this sheet, especially if it was to be printed.

Popularity: 11% [?]