April 24, 2004

Date Clock

This is something I like to have on my files at work. I call it a Date Clock. It doesn't tell the time, just the date, but it's good to have if you can't remember what day or date it is and want to know in a hurry.

In the cell of your choice enter =TODAY(). You can also use =NOW().

This will give today's date (if you use =NOW(), it will also give the time that you open the file, enter the formula for the first time or it is re-calculated. Not so useful if you are just making a date clock).

The date by itself may be useful, but you can improve on it a number of ways. For example, you can right click the cell and select the Format Cells dialog box. Choose the Number tag and then Custom.

Depending on the format that already exists, you might see something like yyyy/mm/dd. You might also guess that 'y' means year, 'm' means month and 'd' means day. You can make your own custom format such as 'd mmmm, yyyy (ddd)'. Note: Don't enter the quotation marks and period in my example, the brackets are optional.

One 'd' or two 'd's will give you the day of the month, three 'd's or four 'd's will give you the weekday. One to four 'm's will give you the month in either numerical or text format, and any number of 'y's will give the year. I notice in my version of Excel that if I enter just one 'y', it automatically enters two 'y's, and if I enter three 'y's it automatically enters four 'y's. Same situation for more than four 'd's, 'm's or 'y's. It seems to be an automatic correction feature.

Anyway, the above 'd mmmm, yyyy (ddd)' custom format should give you 24 April, 2004 (Sat), at least for today. You can also add hyphens, slashes, brackets and spaces as well as text (use commas such as "Today is "). Have fun and experiment, I'll cover other custom format features sometime in the near future.

One last thing, I've noticed one change with later versions of Excel. Depending on the language your system is, formatting will vary accordingly. I have Excel in Japanese so I still get the same results, but not in English which was the default in earlier versions of Excel. You should be able to get around this by selecting the Date format first, then Custom format and adjusting to suit. (To get 24 April, 2004 (Fri) in Excel 2003, Japanese version I now have to enter [$-409]d mmmm, yyyy (ddd);@ to get it in English format because the Japanese format is now default. Of course this is a big improvement for people to have automatic formatting in the same way that they would normally write it. Like I say, experiment and have fun. Excel can be very adaptable.

Comments: Post a Comment

This page is powered by Blogger. Isn't yours?