Share this article...

Microsoft Excel TipsYou can change the way Microsoft Excel formats dates so it displays days and months

What Day of the Week were you Born on?

Formatting dates in excel is fun. Let's play a little game... Do you know what day of the week you were born on? Excel does! Try this...

1. Type your date of birth into a cell. The easiest way to type a date is to use the calculator pad on the right of your keyboard and use the following format date/month number/year e.g. 27/6/61

Excel - Click the green tick to enter into cell2. Now click the green tick at the top (d) to enter it into the cell. Notice the year will change to a 4 figure year (e.g. 1961)

3. Right click on the cell with your date in it and select Format Cells

4. Select Custom in the list on the left.

Excel - Custom format5. Now look at the right hand side where it says Type: Delete the letters dd/mm/yyyy (so it is blank)

6. Now you can format the date how you wish according to these rules:

d = single character date (e.g. in my example 27)

dd = two character date (e.g. still 27 - but if it was the 7th it would display 07)

ddd = three letter day of the week (e.g. Tue)

dddd = full name of day (e.g. Tuesday)

m = single character month (e.g. in my example 6)

mm = two character month (e.g. still 06)

mmm = three letter name of the month (e.g. Jun)

mmmm = full name of month (e.g. June)

yy = two figure year (e.g. 61)

yyyy = four figure year (1961)

Therefore if you type dddd d mmmm yyyy it will display as: Tuesday 27 June 1961

Now you can just type another family members' birth date in that cell using the number keypad as at step 1 and 2 and see what day they were born on.

Tip: I often format dates as d mmm yy (1 Aug 05) so it's a neat short way of displaying dates clearly and are not confused with American formats.

Share this article...
Was this article helpful? Add your comment below...