How to Make a Day Counter in Excel 2007
Do you have a big day coming up in a few months? Maybe you’re getting married or waiting for that first Social Security check. Or maybe you’re doing college on the five-year plan, and can’t wait to graduate. You can make a day counter in Excel that will tell you exactly how many days are left before the big one.
Open a new worksheet in Excel 2007. Click in cell A1. Click Formulas on the ribbon. Click Date & Time. Scroll down the list and pick Today. When the box comes up, click OK. You’ll see today’s date pop into cell A1.
Click in cell A2. Type the date of your big occasion into it. Say it’s May 10, 2010. Type it in this format: 5/10/2010.
Click in cell C4. Under Formulas – Date & Time pick Days360. When the box comes up, type in A1 for Start date. Type in A2 for End date. Then click OK.
You’ll see the number of days between your two dates pop into cell C4. Say the two dates are 10/5/2009 and 5/10/2010. The number 215 will appear in cell C4.
That’s all ok, but how is it going to update itself? Won’t it always say 215? No, because when you entered today’s date into cell A1, that formula was connected to the computer’s clock. That date will change to the correct date every day. So tomorrow, when the date in cell A1 says 10/6/2009, your number of days will say 214. The formula in cell C4 will always calculate the difference between cells A1 and A2, so when cell A1 changes, C4 will change, too.
Now for some fun. We can format the sheet. First, we’ll hide cells A1 and A2. Highlight them both, then go to Home on the Ribbon. In the top middle of the screen you’ll see Number written in blue in a blue box. Click the button just to the right of Number. When the Format Cells box opens, click on the Number tab at the top. In the list under it, click Custom. In the upper middle of the box you’ll see the word Type: and a box to type in just under it. Type in three semicolons like this: ;;; and click OK.
Cells A1 and A2 have disappeared, but they’re just hidden and will still work. To get them back, open the Format Cells box again. Erase the three semicolons, click Date instead of Custom, and click OK. (Don’t type in cells A1 or A2).
In Cell C1 type Days Until I Graduate and format it any way you like. Format the number in cell C4 any way you like. You can make the gridlines disappear and put a background color there instead. Go to Home on the ribbon. On the top left of the screen you’ll see Font written in blue in a blue box. Just above Font you’ll see a big blue A, and just left of that a paint bucket. Highlight all the cells that show on the screen. Click on the menu arrow just right of the paint bucket, and pick a background color for your day counter.
You can put the worksheet on your desktop and look at it twenty times a day!
Picture by Kathleen Murphy