<$BlogRSDURL$>

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.

April 23, 2004

Introduction
Hi there. I'm starting up this blog to give some tips on Excel (Microsoft Spreadsheet Program).

My level now is intermediate but hopefully my tips might be useful to some other people who use Excel.

This idea was suggested to me by my new friend, Masaru Kaji (aka Colo) who is a Microsoft MVP. This stands for Most Valued Professional and is awarded by Microsoft to people who have made major contributions to their field of speciality. You can see his site at this link - Colo's Junk Room

Thanks Colo, hope to meet you soon :)

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